package com.sq.partner.manager.service.impl;

import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.annotation.Resource;
import javax.servlet.http.HttpSession;


import com.sq.partner.manager.common.Config;
import com.sq.partner.manager.util.FreeMarkerUtil;
import com.sq.partner.manager.util.HttpConnectorUtil;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.dom4j.Document;
import org.dom4j.DocumentHelper;
import org.dom4j.Element;
import org.dom4j.Node;
import org.springframework.stereotype.Service;
import org.springframework.web.multipart.MultipartFile;

import com.sq.partner.manager.common.Constants;
import com.sq.partner.manager.common.DictCache;
import com.sq.partner.manager.common.Result;
import com.sq.partner.manager.common.exception.AppException;
import com.sq.partner.manager.dao.ICoreDeptDao;
import com.sq.partner.manager.dao.IFacadeUserDao;
import com.sq.partner.manager.dao.IGatewayAgentInfoMtDao;
import com.sq.partner.manager.dao.IGatewayAgentInfoRelaDao;
import com.sq.partner.manager.dao.IGatewayChannelAppDao;
import com.sq.partner.manager.dao.IGatewayChannelInfoConfigDao;
import com.sq.partner.manager.dao.IGatewaySubChannelDao;
import com.sq.partner.manager.mybatis.model.CoreDept;
import com.sq.partner.manager.mybatis.model.DictDto;
import com.sq.partner.manager.mybatis.model.FacadeUser;
import com.sq.partner.manager.mybatis.model.GatewayChannelApp;
import com.sq.partner.manager.mybatis.model.GatewayChannelInfo;
import com.sq.partner.manager.mybatis.model.GatewaySubChannel;
import com.sq.partner.manager.mybatis.model.GatewayUser;
import com.sq.partner.manager.mybatis.model.TradAgentInfo;
import com.sq.partner.manager.mybatis.model.TradAgentInfoRela;
import com.sq.partner.manager.mybatis.model.TradSaleAgent;
import com.sq.partner.manager.service.IGatewayAgentInfoMtService;
import com.sq.partner.manager.service.IGatewayClearCacheManager;
import com.sq.partner.manager.util.ExcelUtil;
import com.sq.partner.manager.util.LogUtil;
import com.sq.partner.manager.util.MD5Util;
import com.sq.partner.manager.util.Page;
import com.sq.partner.manager.util.StrUtil;

@Service
public class IGatewayAgentInfoMtServiceImpl implements IGatewayAgentInfoMtService {
	private static Logger logger = LogUtil.getLog(IGatewayAgentInfoMtServiceImpl.class); 
	@Resource
	private IGatewayAgentInfoMtDao agentInfoMtDao;
	@Resource
	private IGatewaySubChannelDao subChannelInfoDao;
	@Resource
	private IFacadeUserDao batchMtFacadeUserDao;
	@Resource
	private IFacadeUserDao userdao;
	@Resource
	private IGatewayChannelInfoConfigDao channelInfoConfigDao;
	@Resource
	IGatewayClearCacheManager gatewayClearCacheManager;
	@Resource
	private IGatewaySubChannelDao subChannelDao;
	@Resource
	IGatewayChannelAppDao gatewayChannelAppDao;
	@Resource
	ICoreDeptDao deptDao;
	@Resource
	IGatewayAgentInfoRelaDao agentInfoRelaDao;
	
	private String nocar="2";//跟单费用财险
	private String nlife="3";//跟单费用新意健险
	
	//检查sheet 是否满足业务规则校验,检查个人代理类型的
     List<String> userCodes=new ArrayList<String>();
	Map<String, String> countMap=new HashMap<String, String>();
	Map<String, String> dateMap=new HashMap<String, String>();
	@Override
	public Page<TradAgentInfo> queryInfoByCriteria(Page<TradAgentInfo> page,GatewayUser user) throws Exception {
		
		return agentInfoMtDao.queryInfoByCriteria(page, user);
	}
	
	@Override
	public boolean addAgentMtInfo(TradAgentInfo agent) throws Exception {
		return agentInfoMtDao.addAgentInfo(agent);
	}

	@Override
	public boolean updateAgentMtInfo(TradAgentInfo agent) throws Exception {
		return agentInfoMtDao.updateAgentInfo(agent);
	}

	@Override
	public boolean deleteAgentMtInfo(TradAgentInfo agent) throws Exception {
		return agentInfoMtDao.deleteAgentInfo(agent);
	}

	@Override
	public Page<GatewaySubChannel> queryPageForGatewaySubChannelInfo(Page<GatewaySubChannel> page) throws Exception {
		
		return subChannelInfoDao.queryPageForGatewaySubChannelInfo(page);
	}

	@Override
	public TradAgentInfo queryAgentInfoByCode(TradAgentInfo agent) throws Exception {
		return agentInfoMtDao.queryAgentInfoByCode(agent);
	}

	@Override
	public int checkIsExistConfigPlanInfo(Long agentId) throws Exception {
		return agentInfoMtDao.checkIsExistConfigPlanInfo(agentId);
	}

	@Override
	public List<InputStream> getFileInputStream(MultipartFile[] myfiles) throws AppException {
		List<InputStream> inputStreams=new ArrayList<InputStream>();
		try {
			for(MultipartFile file : myfiles){
				String fileNameStr = file.getOriginalFilename();
				int lastDotIndex = fileNameStr.lastIndexOf(".");
				if(-1==lastDotIndex){
					throw new AppException("请选择扩展名为 .xls 的文件上传");
				}else{
					String extName = fileNameStr.substring(lastDotIndex+1,fileNameStr.length());
					if(!"xls".equals(extName.toLowerCase())){
						throw new AppException("请选择扩展名为 .xls 的文件上传");
					}
				}
				if (file.isEmpty()) {
					throw new AppException("上传失败，上传文件不能为空！");
				} else {
					InputStream inputStream = file.getInputStream();
					inputStreams.add(inputStream);
				}
			}
		} catch (IOException e) {
			logger.info("上传错误!请稍后重试",e);
			throw new AppException("上传错误!请稍后重试");
		}
		return inputStreams;
	}

	@Override
	public synchronized void insertFinalPassDataToDB(InputStream inputStream,GatewayUser user,String agentType, HttpSession session) throws Exception {
		//一、校验excel数据是否合法;
		HSSFWorkbook workBook = null;
		List<TradAgentInfo> list = null;
		try{
			workBook = new HSSFWorkbook(inputStream);
			list = new ArrayList<TradAgentInfo>();
		}catch(Exception e){
			logger.info("上传失败，上传文件内容有误",e);
			throw new AppException("上传失败，上传文件内容有误!");
		}
		//读取模板第一个sheet表格的数据
		HSSFSheet sheet = workBook.getSheetAt(0);
		int sheets=workBook.getNumberOfSheets();
		HSSFSheet sheet1 =null;
		HSSFSheet sheet2 =null;
		HSSFSheet sheet3 =null;
		boolean flag=false;
		boolean flag1=false;
		boolean flag3=false;
		/**联合出单方案关联key值列表**/
		List<String> commPlanAll=new ArrayList<String>();/**模板所有key值*/
		/**联合出单方案关联key值列表**/
		 List<String> commPlanUpdate=new ArrayList<String>();/**模板已存在key值*/
		if(sheets>1){
			sheet1= workBook.getSheetAt(1);
			String sheetName=workBook.getSheetName(1);
			if(StrUtil.isNotEmpty(sheetName)&&sheetName.contains("销售信息")){
				flag=true;
			}
			sheet2= workBook.getSheetAt(2);
			String sheetName1=workBook.getSheetName(2);
			if(StrUtil.isNotEmpty(sheetName1)&&sheetName1.contains("销售信息")){
				flag1=true;
			}
			sheet3 = workBook.getSheetAt(3);
			String sheetName3 = workBook.getSheetName(3);
			if(StrUtil.isNotEmpty(sheetName3)&&sheetName3.contains("费用信息")){
				flag3=true;
			}
				
		}
		int lastRowNumber = sheet.getLastRowNum();
		int lastRow=0;
		int lastRow1=0;
		int lastRow3=0;
	    
		if("3".equals(agentType)||"4".equals(agentType)||"5".equals(agentType)){
			if(sheet1!=null){
				lastRow=sheet1.getLastRowNum();
			}
		}
		if("3".equals(agentType)||"4".equals(agentType)||"5".equals(agentType)){
			if(sheet2!=null){
				lastRow1=sheet2.getLastRowNum();
			}
		}
		//新增Sheet 车险-费用信息
		if("4".equals(agentType)){ 
			if(sheet3!=null){
				lastRow3=sheet3.getLastRowNum();
			}
		}
		boolean content=false;
		if("5".equals(agentType)){
			content=lastRowNumber<=3;
		}else{
			content=lastRowNumber<=1;
		}
		Map<String, List<TradAgentInfo>> agentMap=new HashMap<String, List<TradAgentInfo>>();
		if(content&&lastRow<3&&lastRow1<3){
			throw new AppException("上传失败，上传excel文件内容为空或模板非最新模板");
		}else if(lastRowNumber>101||lastRow>101){
			throw new AppException("为了用户体验，一次最多只能导入100条数据");
		}
		//1.导入DB前，先检查excel是否满足业务需求规则；
		if("3".equals(agentType)&&lastRowNumber>1){
			list = importPreCheckIsPassBusinessRule(sheet,user);
			StringBuffer errorBuf = new StringBuffer();
			List<TradAgentInfo> errorUserMsg = null;
			List<TradAgentInfo> allChannelInfo = null;
			int errorCount = 0;
			//查询IN条件拼接的字符串
			String inSubDBTemConcatStr="";
			String inChannelDBTemConcatStr="";
			
			//(1)所有的channelCode集合。
			Set<String> channelCodes = new HashSet<String>();
			for(int i=0;i<list.size();i++){
				channelCodes.add(list.get(i).getChannelCode());
			}
			//(2)map集合
			Map<String,List<TradAgentInfo>> agentInfos = new HashMap<String,List<TradAgentInfo>>();
			for (String string : channelCodes) {
				List<TradAgentInfo> agList = new ArrayList<TradAgentInfo>();
				for(int i=0;i<list.size();i++){
					if(string.equals(list.get(i).getChannelCode())){
						agList.add(list.get(i));
					}
				}
				agentInfos.put(string, agList);
			}
			//(3)循环校验
			for (String key : agentInfos.keySet()) {
				errorUserMsg=new ArrayList<TradAgentInfo>();
				/**初始化map*/
				Map<String, TradAgentInfo> userMap=new HashMap<String, TradAgentInfo>();
				//拼接合作伙伴代码
				inChannelDBTemConcatStr+="'"+key+"',";
				List<TradAgentInfo> checkList = agentInfos.get(key);
				List<TradAgentInfo> checkList1=new ArrayList<TradAgentInfo>();
				checkList1.addAll(checkList);
				//提高性能算法从DB检查次数
				int totalRowCounts = checkList.size();
				int checkFromDBCount=0;
				
				//每500条从数据库中查询是否满足条件
				if(totalRowCounts/500<0){
					checkFromDBCount=1;
				}else if(totalRowCounts/500>0){
					checkFromDBCount = totalRowCounts/500;
				}
				//声明个装载检查标识的容器
				List<Integer> checkDBCountLs = new  ArrayList<Integer>(checkFromDBCount);
				for(int i=1;i<=checkFromDBCount;i++){
					checkDBCountLs.add(Integer.valueOf(i*500));
				}
				
				//2.检查满足业务规范的数据进一步DB检查
				for(int i=0;i<checkList.size();i++){
					userMap.put(checkList.get(i).getUserCode(), checkList.get(i));
					if(checkDBCountLs.contains(i)){
						//DB检查数据库
						errorUserMsg = agentInfoMtDao.searchExistUserCodeInfoFromDB(key,inSubDBTemConcatStr.substring(0,inSubDBTemConcatStr.length()-1));
						inSubDBTemConcatStr="";//清空
					}
					inSubDBTemConcatStr += "'"+list.get(i).getUserCode()+"',";
				}
				//追后一次检查DB
				if(StrUtil.isNotEmpty(inSubDBTemConcatStr)){
					//最后一次DB检查数据库
					List<TradAgentInfo> errorUserFinalMsg = agentInfoMtDao.searchExistUserCodeInfoFromDB(key,inSubDBTemConcatStr.substring(0,inSubDBTemConcatStr.length()-1));
					errorUserMsg.addAll(errorUserFinalMsg);
					inSubDBTemConcatStr="";//清空
				}
				/**取出所有已存在的用户*/
				List<TradAgentInfo>  agents=agentMap.get("update");
				for (TradAgentInfo info : errorUserMsg) {
					if(userMap.get(info.getUserCode())!=null){
						if(agents==null){
							agents=new ArrayList<TradAgentInfo>();
						}
						checkList1.remove(userMap.get(info.getUserCode()));
						/**设置id*/
						TradAgentInfo upinfo=userMap.get(info.getUserCode());
						upinfo.setId(info.getId());
						agents.add(upinfo);
					}
				}
				agentMap.put("update", agents);
				/**取出所有需要保存的代理信息**/
				List<TradAgentInfo> adds=agentMap.get("add");
				if(adds==null){
					adds=checkList1;
				}else{
					adds.addAll(checkList1);
				}
				agentMap.put("add", adds);
			}

			
			//检查该用户下的所有的channelInfo是否满足权限并存在
			allChannelInfo = agentInfoMtDao.searchExistChannelInfoFromDB(user.getDepartmentStr());
			if(null!=allChannelInfo && allChannelInfo.size()>0){
				//存取检查对象
				List<String> temChannelLs = new ArrayList<String>();
				for(TradAgentInfo tem :allChannelInfo){
					temChannelLs.add(tem.getChannelCode());
				}
				String[] StrArray = inChannelDBTemConcatStr.substring(0,inChannelDBTemConcatStr.length()-1).replaceAll("'", "").split(",");
				for(String tem :StrArray){
					if(errorCount>9){
						throw new AppException("上传失败:<br>"+errorBuf.toString());
					}
					if(!temChannelLs.contains(tem)){
						errorBuf.append("合作伙伴代码 "+tem+" 不存在或没有相关权限，请全列检查修改<br>");
						errorCount++;
						inChannelDBTemConcatStr ="";//清空
					}
				}
			}else{
				errorBuf.append("当前登陆用户权限下不存在任何合作伙伴信息，请先申请权限后后导入<br>");
				errorCount++;
				inChannelDBTemConcatStr ="";//清空
			}
			//若有错误提示信息，则提示
			if(StrUtil.isNotEmpty(errorBuf.toString())){
				throw new AppException("上传失败:<br>"+errorBuf.toString());
			}
			
			
			/**
			 * 查询合作伙伴为界面出单的。
			 * 添加到user表
			 */
			List<GatewayChannelInfo> channelInfos = null;
			try {
				channelInfos = channelInfoConfigDao.queryChannelsByChannelCodeStr(inChannelDBTemConcatStr.substring(0,inChannelDBTemConcatStr.length()-1));
			} catch (Exception e) {
				logger.info("查询界面出单合作伙伴失败",e);
				throw new AppException("查询界面出单合作伙伴失败");
			}
			
			List<FacadeUser> errorUsers = null;
			String facadeUserCodeStr = "";
			//销管校验专用异常参数
			String errorResult = "";
			int errorConunt = 0;
			for (int i = 0; i < list.size(); i++) {
				//循环请求交易系统，校验数据合法性
				Map<String, String> resultMap = agentInfoValidation(list.get(i));
				if (!"0000".equals(resultMap.get("result"))){
					errorResult += "第"+(i+3)+"条数据中："+ resultMap.get("result") + "</br>";
					errorConunt += 1;
				}
			}
			if (errorConunt > 0){
				throw new AppException(errorResult);
			}

			//都满足条件后，进行批量插入;
			List<FacadeUser> userList = new ArrayList<FacadeUser>();
			if(channelInfos!=null&&!channelInfos.isEmpty()){
				for (GatewayChannelInfo gatewayChannelInfo : channelInfos) {
					errorUsers=new ArrayList<FacadeUser>();
					List<TradAgentInfo> checkList = agentInfos.get(gatewayChannelInfo.getChannelCode());
					//提高性能算法从DB检查次数
					int totalRowCounts = checkList.size();
					int checkFromDBCount=0;
					
					//每500条从数据库中查询是否满足条件
					if(totalRowCounts/500<0){
						checkFromDBCount=1;
					}else if(totalRowCounts/500>0){
						checkFromDBCount = totalRowCounts/500;
					}
					//声明个装载检查标识的容器
					List<Integer> checkDBCountLs = new  ArrayList<Integer>(checkFromDBCount);
					for(int i=1;i<=checkFromDBCount;i++){
						checkDBCountLs.add(Integer.valueOf(i*500));
					}
					//2.检查满足业务规范的数据进一步DB检查
					for(int i=0;i<checkList.size();i++){
						if(checkDBCountLs.contains(i)){
							//DB检查数据库
							try {
								errorUsers = userdao.queryUsersByUserCodeStr(facadeUserCodeStr.substring(0, facadeUserCodeStr.length()-1));
							} catch (Exception e) {
								logger.info("查询重复用户名出错",e);
								throw new AppException("查询重复用户名出错。");
							}
							facadeUserCodeStr="";//清空
							/*if((null!=errorUsers && errorUsers.size()>0)){
								break;
							}*/
						}
						facadeUserCodeStr += "'"+list.get(i).getUserCode()+"',";
					}
				/*	//检查userCode
					if(null!=errorUsers && errorUsers.size()>0){
						for(FacadeUser tem :errorUsers){
							if(errorCount>9){
								throw new AppException("上传失败:<br>"+errorBuf.toString());
							}
							errorBuf.append("合作伙伴："+gatewayChannelInfo.getChannelCode()+"下，用户名"+tem.getUserCode()+"在t_Facade_user表已经存在，请修改<br>");
							errorCount++;
						}
					}*/
					//追后一次检查DB
					if(StrUtil.isNotEmpty(facadeUserCodeStr)){
						//最后一次DB检查数据库
						List<FacadeUser> errorUserFinalMsg = null;
						try {
							errorUserFinalMsg =  userdao.queryUsersByUserCodeStr(facadeUserCodeStr.substring(0, facadeUserCodeStr.length()-1));
							errorUsers.addAll(errorUserFinalMsg);
						} catch (Exception e) {
							logger.info(e);
						}
						inSubDBTemConcatStr="";//清空
					}
					Map<String , FacadeUser> useMap=new HashMap<String, FacadeUser>();
					/**循环所有存在的用户*/
					for (FacadeUser use: errorUsers) {
						useMap.put(use.getUserCode(), use);
					}
					//修复原有bug
					if(StrUtil.isNotEmpty(errorBuf.toString())){
						throw new AppException("上传失败:<br>"+errorBuf.toString());
					}
					for(TradAgentInfo temAgent:checkList){
						if(useMap.get(temAgent.getUserCode())!=null){
							continue;
						}
						FacadeUser facadeUser = new FacadeUser();
						facadeUser.setUserCode(temAgent.getUserCode());
						facadeUser.setUserName(temAgent.getUserName());
						facadeUser.setUserTypeCode("3");
						if(StrUtil.isNotEmpty(temAgent.getSubChannelCode())){
							facadeUser.setfKSubChannelCode(temAgent.getSubChannelCode());
						}else{
							facadeUser.setfKSubChannelCode(temAgent.getChannelCode());
						}
						facadeUser.setFkChannelCode(temAgent.getChannelCode());
						facadeUser.setStatus(1);
						facadeUser.setUserPwd(MD5Util.md5(temAgent.getUserCode()));
						//facadeUser.setOperTime(new Date());
						facadeUser.setOperUser(user.getUserid());
						//添加到facadeUser
						userList.add(facadeUser);
					}
				}
				if(null!=userList && userList.size()>0){
					batchMtFacadeUserDao.addBatchMtFacadeUserInfo(userList);
				}
			}
			
		//对总对总代理类型的导入处理
		}else if("4".equals(agentType)&&lastRowNumber>1){
			list = importPreCheckSubBranchAgentTypeIsPassBusinessRule(sheet,user);
			StringBuffer errorBuf = new StringBuffer();
			Map<String,Integer> excelIndexSubChannelInfo = new HashMap<String,Integer>();
			Map<String,Integer> excelIndexChannelInfo = new HashMap<String,Integer>();
			List<TradAgentInfo> errorUserMsg = null;
			List<TradAgentInfo> allChannelInfo = null;
			//总对总交易类型合作伙伴容器
			List<String> isFacadeChannelInfoLs = null;
			List<TradAgentInfo> isFacadeAgentList = null;//装载界面出单的合作伙伴的代理信息容器
			int errorCount = 0;
			//查询IN条件拼接的字符串
			String inSubDBTemConcatStr="";
			String channoCodeStr="";
			String inChannelDBTemConcatStr="";
			//提高性能算法从DB检查次数
			int totalRowCounts = sheet.getLastRowNum()+1;
			int checkFromDBCount=0;
			//每300条从数据库中查询是否满足条件
			if(totalRowCounts/300<0){
				checkFromDBCount=1;
			}else if(totalRowCounts/300>0){
				checkFromDBCount = totalRowCounts/300;
			}
			//声明个装载检查标识的容器
			List<Integer> checkDBCountLs = new  ArrayList<Integer>(checkFromDBCount);
			for(int i=1;i<=checkFromDBCount;i++){
				checkDBCountLs.add(Integer.valueOf(i*300));
			}
			//2.检查满足业务规范的数据进一步DB检查
			//2.1检查该用户下的所有的channelInfo是否满足权限并存在 且区分合作伙伴交易合作类型
			allChannelInfo = agentInfoMtDao.searchExistChannelInfoFromDB(user.getDepartmentStr());
			if(null!=allChannelInfo && allChannelInfo.size()>0){
				isFacadeChannelInfoLs = new ArrayList<String>(5);
				isFacadeAgentList = new ArrayList<TradAgentInfo>();
				for(TradAgentInfo tem1 :allChannelInfo){
					if("1".equals(tem1.getTransType())){//界面出单
						isFacadeChannelInfoLs.add(tem1.getChannelCode());
					}
				}
			}else{
				throw new AppException("上传失败:<br>当前登陆用户权限下不存在任何合作伙伴信息，请先申请权限后后导入<br>");
			}
			Map<String, String> map=new HashMap<String, String>();
			//2.2检查 subCode和用户代码 和 用户姓名是否为空
			Map<String, TradAgentInfo> channelMap=new HashMap<String, TradAgentInfo>();
			List<TradAgentInfo> infoList=new ArrayList<TradAgentInfo>();
			if(null!=list && list.size()>0){
				errorUserMsg=new ArrayList<TradAgentInfo>();
				infoList.addAll(list);
				for(int i=0;i<list.size();i++){
					channelMap.put(list.get(i).getChannelCode()+list.get(i).getSubChannelCode(), list.get(i));
					if(errorCount>9){
						excelIndexSubChannelInfo.clear();
						throw new AppException("上传失败:<br>"+errorBuf.toString());
					}
					if(checkDBCountLs.contains(i)){
						//DB检查数据库
						errorUserMsg = agentInfoMtDao.searchExistSubBranchCodeInfoFromDB(map);
						inSubDBTemConcatStr="";//清空
						channoCodeStr="";
						map=new HashMap<String, String>();
						/*if((null!=errorUserMsg && errorUserMsg.size()>0)){
							break;
						}*/
					}
					//检查合作伙伴是界面出单时，用户代码和用户姓名是否为空
					if(null!=isFacadeChannelInfoLs && isFacadeChannelInfoLs.size()>0){
						if(isFacadeChannelInfoLs.contains(list.get(i).getChannelCode())){
							if(StrUtil.isNotEmpty(list.get(i).getUserCode()) && StrUtil.isEmpty(list.get(i).getUserName())){
								errorBuf.append("第 "+list.get(i).getExcelRowIndex()+"行合作伙伴"+list.get(i).getChannelCode()+"的合作方式为界面出单，姓名不允许为空，请全列检查<br>");
								errorCount++;
							}else if(StrUtil.isNotEmpty(list.get(i).getUserName()) && StrUtil.isEmpty(list.get(i).getUserCode())){
								errorBuf.append("第 "+list.get(i).getExcelRowIndex()+"行合作伙伴"+list.get(i).getChannelCode()+"的合作方式为界面出单，用户名不允许为空，请全列检查<br>");
								errorCount++;
							}else if(StrUtil.isNotEmpty(list.get(i).getUserCode()) && StrUtil.isNotEmpty(list.get(i).getUserName())){
								isFacadeAgentList.add(list.get(i));
							}
						}
					}
					inSubDBTemConcatStr += "'"+list.get(i).getChannelCode()+list.get(i).getSubChannelCode()+"',";
				/*	channoCodeStr+="'"+list.get(i).getChannelCode()+"',";*/
					map.put("flag", inSubDBTemConcatStr.substring(0, inSubDBTemConcatStr.length()-1));
				/*	map.put("channoCode", channoCodeStr.substring(0, channoCodeStr.length()-1));*/
					//拼接channelCode
					if(!(inChannelDBTemConcatStr.contains(list.get(i).getChannelCode()))){
						inChannelDBTemConcatStr+= list.get(i).getChannelCode()+",";
					}
					excelIndexSubChannelInfo.put(list.get(i).getSubChannelCode(),list.get(i).getExcelRowIndex());//记录重复的userCode
					excelIndexChannelInfo.put(list.get(i).getChannelCode(),list.get(i).getExcelRowIndex());
				}
			}
			/*//检查subCode
			if(null!=errorUserMsg && errorUserMsg.size()>0){
				for(TradAgentInfo tem :errorUserMsg){
					if(errorCount>9){
						excelIndexSubChannelInfo.clear();
						throw new AppException("上传失败:<br>"+errorBuf.toString());
					}
					errorBuf.append("第 "+excelIndexSubChannelInfo.get(tem.getSubChannelCode())+"行 的区域代码、网点代码、合作伙伴组成的唯一标识"+tem.getSubChannelCode()+"已经存在，请修改<br>");
					errorCount++;
				}
			}*/
			//最后一次检查DB
			if(StrUtil.isNotEmpty(inSubDBTemConcatStr)){
				//最后一次DB检查数据库
				List<TradAgentInfo> errorUserFinalMsg = agentInfoMtDao.searchExistSubBranchCodeInfoFromDB(map);
				errorUserMsg.addAll(errorUserFinalMsg);
/*				if(null!=errorUserFinalMsg && errorUserFinalMsg.size()>0){
					for(TradAgentInfo tem :errorUserFinalMsg){
						if(errorCount>9){
							excelIndexSubChannelInfo.clear();
							throw new AppException("上传失败:<br>"+errorBuf.toString());
						}
						errorBuf.append("第 "+excelIndexSubChannelInfo.get(tem.getSubChannelCode())+"行 的区域代码、网点代码、合作伙伴组成的唯一标识"+tem.getSubChannelCode()+"已经存在，请修改<br>");
						errorCount++;
					}
				}
*/				
			}
			/**取出所有已存在的用户*/
			List<TradAgentInfo>  agents=agentMap.get("update");
			for (TradAgentInfo info : errorUserMsg) {
				if(channelMap.get(info.getChannelCode()+info.getSubChannelCode())!=null){
					if(agents==null){
						agents=new ArrayList<TradAgentInfo>();
					}
					infoList.remove(channelMap.get(info.getChannelCode()+info.getSubChannelCode()));
					/**设置id*/
					TradAgentInfo upinfo=channelMap.get(info.getChannelCode()+info.getSubChannelCode());
					upinfo.setId(info.getId());
					agents.add(upinfo);
				}
			}
			agentMap.put("update", agents);
			/**取出所有需要保存的代理信息**/
			List<TradAgentInfo> adds=agentMap.get("add");
			if(adds==null){
				adds=infoList;
			}else{
				adds.addAll(infoList);
			}
			agentMap.put("add", adds);
			inSubDBTemConcatStr="";//清空
			//若有错误提示信息，则提示
			if(StrUtil.isNotEmpty(errorBuf.toString())){
				throw new AppException("上传失败:<br>"+errorBuf.toString());
			}
			excelIndexSubChannelInfo.clear();//清理掉map
			//2.3检查出单用户中的userCode是否有重复；
			if(null!=isFacadeAgentList && isFacadeAgentList.size()>0){
				for(int i = 0; i < isFacadeAgentList.size() - 1; i++){
					String userCode = isFacadeAgentList.get(i).getUserCode();
					if(errorCount>9){
						throw new AppException("上传失败:<br>"+errorBuf.toString());
					}
					for(int j = i + 1; j < isFacadeAgentList.size(); j++){
						if(userCode.equals(isFacadeAgentList.get(j).getUserCode())){
							errorBuf.append(" 第  "+isFacadeAgentList.get(i).getExcelRowIndex()+" 行与第 "+isFacadeAgentList.get(j).getExcelRowIndex()+" 行用户代码重复，请修改 <br>");
							errorCount++;
							continue;
						}
					}
				}
			}
			//若有错误提示信息，则提示
			if(StrUtil.isNotEmpty(errorBuf.toString())){
				throw new AppException("上传失败:<br>"+errorBuf.toString());
			}
			//allChannelInfo = agentInfoMtDao.searchExistChannelInfoFromDB(user.getDepartmentStr());
			//2.4检查权限合作伙伴权限是否满足
			List<String> temChannelLs = new ArrayList<String>();
			for(TradAgentInfo tem :allChannelInfo){
				temChannelLs.add(tem.getChannelCode());
			}
			String[] StrArray = inChannelDBTemConcatStr.substring(0,inChannelDBTemConcatStr.length()-1).split(",");
			for(String tem :StrArray){
				if(errorCount>9){
					excelIndexChannelInfo.clear();
					throw new AppException("上传失败:<br>"+errorBuf.toString());
				}
				if(!temChannelLs.contains(tem)){
					errorBuf.append("第 "+excelIndexChannelInfo.get(tem)+"行的合作伙伴代码 "+tem+" 不存在或没有相关权限，请全列检查修改<br>");
					errorCount++;
				}
			}
			inChannelDBTemConcatStr ="";//清空
			//若有错误提示信息，则提示
			if(StrUtil.isNotEmpty(errorBuf.toString())){
				throw new AppException("上传失败:<br>"+errorBuf.toString());
			}
			excelIndexChannelInfo.clear();//清理掉map
			//2.5若存在总对总界面出单类型，则检查 userCode 是否在 t_facade_user表存在
			List<FacadeUser> errorExistUsers = new ArrayList<FacadeUser>();
			if(null!=isFacadeAgentList && isFacadeAgentList.size()>0){
				Map<String,Integer> userIndexMap = new HashMap<String, Integer>();
				String facadeUserCodeStr = "";
				for(int i= 0 ;i<isFacadeAgentList.size();i++){
					if(checkDBCountLs.contains(i)){
						try {
							errorExistUsers.addAll(userdao.queryUsersByUserCodeStr(facadeUserCodeStr.substring(0, facadeUserCodeStr.length()-1)));
						} catch (Exception e) {
							logger.error(e);
							throw new AppException("上传失败:查询用户名错误，请稍后重试！<br>");
						}
						facadeUserCodeStr="";//清空
/*						if((null!=errorExistUsers && errorExistUsers.size()>0)){
							break;
						}
*/					}
					facadeUserCodeStr += "'"+list.get(i).getUserCode()+"',";
					userIndexMap.put(list.get(i).getUserCode(), list.get(i).getExcelRowIndex());
				}
				
/*				//检查userCode
				if(null!=errorExistUsers && errorExistUsers.size()>0){
					for(FacadeUser tem2 :errorExistUsers){
						if(errorCount>9){
							throw new AppException("上传失败:<br>"+errorBuf.toString());
						}
						errorBuf.append("第 "+userIndexMap.get(tem2.getUserCode())+"行用户名："+tem2.getUserCode()+"在t_Facade_user表已经存在，请修改<br>");
						errorCount++;
					}
				}*/
				//追后一次检查DB
				if(StrUtil.isNotEmpty(facadeUserCodeStr)){
					//最后一次DB检查数据库
					try {
						errorExistUsers.addAll(userdao.queryUsersByUserCodeStr(facadeUserCodeStr.substring(0, facadeUserCodeStr.length()-1))) ;
					} catch (Exception e) {
						logger.error(e);
						throw new AppException("上传失败:查询用户名错误，请稍后重试！<br>");
					}
					facadeUserCodeStr="";//清空
		/*			if(null!=errorExistUsers && errorExistUsers.size()>0){
						for(FacadeUser tem3 :errorExistUsers){
							if(errorCount>9){
								throw new AppException("上传失败:<br>"+errorBuf.toString());
							}
							errorBuf.append("第 "+userIndexMap.get(tem3.getUserCode())+"行用户名："+tem3.getUserCode()+"在t_Facade_user表已经存在，请修改<br>");
							errorCount++;
						}
					}*/
				}
				
			}
			/**将所有已存在的usercode存入map**/
			Map<String, FacadeUser> userMap1=new HashMap<String, FacadeUser>();
			for (FacadeUser facadeUser : errorExistUsers) {
				userMap1.put(facadeUser.getUserCode(), facadeUser);
			}
			//若有错误提示信息，则提示
			if(StrUtil.isNotEmpty(errorBuf.toString())){
				throw new AppException("上传失败:<br>"+errorBuf.toString());
			}
			//销管校验专用异常参数
			String errorResult = "";
			int errorConunt = 0;
			for (int i = 0; i < list.size(); i++) {
				//循环请求交易系统，校验数据合法性
				if (list.get(i).getCategoryCode().length() != 5){
					errorResult += "第"+(i+3)+"条数据中："+ "原业务来源只能为5位" + "</br>";
					errorConunt += 1;
				}
				Map<String, String> resultMap = agentInfoValidation(list.get(i));
				if (!"0000".equals(resultMap.get("result"))){
					errorResult += "第"+(i+3)+"条数据中："+ resultMap.get("result") + "</br>";
					errorConunt += 1;
				}
			}
			if (errorConunt > 0){
				throw new AppException(errorResult);
			}
			List<TradAgentInfo> subChannelList=agentMap.get("add");
			//3.都满足条件后，进行批量插入;
			List<GatewaySubChannel> userList = new ArrayList<GatewaySubChannel>();
			if(null!=list && list.size()>0){
				for(TradAgentInfo temAgent:subChannelList){
					//首先先检查否存在该子机构的信息
					GatewaySubChannel subChannelInfo = new GatewaySubChannel();
					subChannelInfo.setSubChannelCode(temAgent.getSubChannelCode());
					subChannelInfo.setSubChannelName(temAgent.getBranchName());
					subChannelInfo.setParentChannelCode(temAgent.getChannelCode());
					subChannelInfo.setZone(temAgent.getZone());
					subChannelInfo.setBranch(temAgent.getBranch());
					subChannelInfo.setBranchName(temAgent.getBranchName());
					subChannelInfo.setFkChannelCode(temAgent.getChannelCode());
					subChannelInfo.setOperUser(user.getUserid());
					//添加到facadeUser
					userList.add(subChannelInfo);
				}
			}
			if(null!=userList && userList.size()>0){
				subChannelInfoDao.addBatchMtSubBranchInfo(userList);
			}
			if(null!=isFacadeAgentList && isFacadeAgentList.size()>0){
				List<FacadeUser> usersLs = new ArrayList<FacadeUser>();
				for(TradAgentInfo temUserInfo:isFacadeAgentList){
					/**已存在的usercode不保存*/
					if(userMap1.get(temUserInfo.getUserCode())!=null){
						continue;
					}
					FacadeUser facadeUser = new FacadeUser();
					facadeUser.setUserCode(temUserInfo.getUserCode());
					facadeUser.setUserName(temUserInfo.getUserName());
					facadeUser.setUserTypeCode("4");
					facadeUser.setfKSubChannelCode(temUserInfo.getSubChannelCode());
					facadeUser.setStatus(1);
					facadeUser.setFkChannelCode(temUserInfo.getChannelCode());
					facadeUser.setUserPwd(MD5Util.md5(temUserInfo.getUserCode()));
					//facadeUser.setOperTime(new Date());
					facadeUser.setOperUser(user.getUserid());
					//添加到facadeUser
					usersLs.add(facadeUser);
				}
				if(null!=usersLs && usersLs.size()>0){
					batchMtFacadeUserDao.addBatchMtFacadeUserInfo(usersLs);
				}
			}
		//对联合出单代理类型的导入处理
		}else if("5".equals(agentType)&&lastRowNumber>3){
			/**需要更新的代理信息列表*/
			List<TradAgentInfo>  comAgents=agentMap.get("update");
			/**需要添加的代理信息列表**/
			List<TradAgentInfo> comadds=agentMap.get("add");
			if(comAgents==null){
				comAgents=new ArrayList<TradAgentInfo>();
			}
			if(comadds==null){
				comadds=new ArrayList<TradAgentInfo>();
			}
			/**所有需要导入的代理信息**/
			List<TradAgentInfo> infoAll=new ArrayList<TradAgentInfo>();
			list = CheckCombinedSubBranchAgentType(sheet,user);
			int totalRowCounts = list.size();
			int checkFromDBCount=0;
			//每300条从数据库中查询是否满足条件
			if(totalRowCounts/200<0){
				checkFromDBCount=1;
			}else if(totalRowCounts/200>0){
				checkFromDBCount = totalRowCounts/200;
			}
			//声明个装载检查标识的容器
			List<Integer> checkDBCountLs = new  ArrayList<Integer>(checkFromDBCount);
			for(int i=1;i<=checkFromDBCount;i++){
				checkDBCountLs.add(Integer.valueOf(i*200));
			}
			StringBuffer errorBuf = new StringBuffer();
			String comStr="";
			/**数据库里查询所有已存在的key*/
			List<TradAgentInfo> comInfo=new ArrayList<TradAgentInfo>();
			/**所有数据map**/
			Map<String,TradAgentInfo > comMap=new HashMap<String, TradAgentInfo>();
			if(null != list && list.size()>0){
				/**将所有需要导入的代理信息加入到列表中**/
				infoAll.addAll(list);
				for (int i = 0; i < list.size(); i++) {
					comMap.put(list.get(i).getUnionType()+"-"+list.get(i).getKey1()+"-"+list.get(i).getKey2()+"-"+list.get(i).getKey3()+"-"+list.get(i).getKey4()+"-"+list.get(i).getKey5(), list.get(i));
					if(checkDBCountLs.contains(i)){
						comInfo.addAll(agentInfoMtDao.queryAgentInByMap1(comStr.substring(0,comStr.length()-1)));
					
						comStr="";
					}
					String str=list.get(i).getUnionType()+"-"+list.get(i).getKey1()+"-"+list.get(i).getKey2()+"-"+list.get(i).getKey3()+"-"+list.get(i).getKey4()+"-"+list.get(i).getKey5();
					comStr+="'"+str+"',";
					commPlanAll.add(str);
				}
				if(StrUtil.isNotEmpty(comStr)){
					comInfo.addAll(agentInfoMtDao.queryAgentInByMap1(comStr.substring(0,comStr.length()-1)));
					comStr="";
				}
			}
			for (TradAgentInfo tradAgentInfo : comInfo) {
				TradAgentInfo info=comMap.get(tradAgentInfo.getChannelCode());
				if(info!=null){
					infoAll.remove(info);
					info.setId(tradAgentInfo.getId());
					/**已存在keys列表*/
					commPlanUpdate.add(info.getId()+"");
					comAgents.add(info);
				}
			}
			//销管校验专用异常参数
			String errorResult = "";
			int errorConunt = 0;
			for (int i = 0; i < list.size(); i++) {
				//循环请求交易系统，校验数据合法性
				Map<String, String> resultMap = agentInfoValidation(list.get(i));
				if (!"0000".equals(resultMap.get("result"))){
					errorResult += "第"+(i+3)+"条数据中："+ resultMap.get("result") + "</br>";
					errorConunt += 1;
				}
			}
			if (errorConunt > 0){
				throw new AppException(errorResult);
			}
			agentMap.put("update", comAgents);
			agentMap.put("add", infoAll);
			//若有错误提示信息，则提示
			if(StrUtil.isNotEmpty(errorBuf.toString())){
				throw new AppException("上传失败:<br>"+errorBuf.toString());
			}
		}
		/**广东费用跟单导入**/
		/**财险*/
		/***个人代理*/
		List<TradSaleAgent> saleAgents=new ArrayList<TradSaleAgent>();
		List<TradSaleAgent> saleAgents1=new ArrayList<TradSaleAgent>();
		
		if("3".equals(agentType)&&lastRow>=3&&flag){
			saleAgents=importPreCheckIsPassBusinessRule1(sheet1,user,list);
		}
		if("4".equals(agentType)&&lastRow>=3&&flag){
			saleAgents=importPreCheckSubBranchAgentTypeIsPassBusinessRule1(sheet1, user, list);
		}
		if("5".equals(agentType)&&lastRow>=3&&flag){
			saleAgents=importPreCheckIsPassBusinessRule2(sheet1,user,list);
		}
		/**需要更新的代理信息*/
		List<TradAgentInfo> updateInfo=agentMap.get("update");
		/**需要添加的代理信息*/
		List<TradAgentInfo> addInfo=agentMap.get("add");
		/***所有需要判断的广东渠道信息*/
		List<TradAgentInfo> saleAgentInfo=new ArrayList<TradAgentInfo>();
		/**新增信息全部判断*/
		saleAgentInfo.addAll(addInfo);
		/**需要更新的代理信息**/
		Integer update=0;
		List<TradAgentInfo> updateSales=new ArrayList<TradAgentInfo>();
		/**更新的代理信息是否存在已配置广东信息的*/
		if(null!=updateInfo && updateInfo.size()>0){
			List<String> agentInfoIds=saleListUpdate(updateInfo);
			Map<String, Object> map=new HashMap<String, Object>();
			map.put("ids", agentInfoIds);
			if(agentInfoIds!=null&&agentInfoIds.size()>0){
				updateSales=agentInfoMtDao.isExtTradPlanSale(map);
			}
		}
		/**判断是否有广东机构*/
		if(lastRow1<3){
			/**更新的代理信息存在未配置广东销售，同时新意意健险广东未导入**/
			if(updateSales!=null&&updateSales.size()>0){
				throw new AppException("上传失败:广东地区必须配置新意健险-广东销售信息");	
			}
			/**添加代理信息，为配置新意健险广东信息*/
			if(saleAgentInfo!=null){
				for (int i = 0; i < saleAgentInfo.size(); i++) {
					if(saleAgentInfo.get(i).getDptCde().startsWith("44")){
						if("3".equals(agentType)){
							throw new AppException("上传失败:用户名为"+saleAgentInfo.get(i).getUserCode()+"请配置新意健险-广东销售信息");	
						}else if ("4".equals(agentType)){
							throw new AppException("上传失败:网点代码为"+saleAgentInfo.get(i).getBranch()+"请配置新意健险-广东销售信息");	
						}else if ("5".equals(agentType)){
							TradAgentInfo agentinfo=saleAgentInfo.get(i);
							throw new AppException("上传失败:key值分别为"+agentinfo.getKey1()+"-"+agentinfo.getKey2()+"-"+agentinfo.getKey3()+"-"+agentinfo.getKey4()+"-"+agentinfo.getKey5()+"请配置新意健险-广东销售信息");	
						}
						
					}	
				} 	
			}
		}
		saleAgentInfo.addAll(updateSales);
		/**新意健险*/
		if("3".equals(agentType)&&lastRow1>=3&&flag1){
			saleAgents1=importNlifeCheckIsPassBusinessRule1(sheet2,user,list,saleAgentInfo);
		}
		if("4".equals(agentType)&&lastRow1>=3&&flag1){
			saleAgents1=importNlifeCheckSubBranchAgentTypeIsPassBusinessRule(sheet2, user, list,saleAgentInfo);
		}
		if("5".equals(agentType)&&lastRow1>=3&&flag1){
			saleAgents1=importNlifeCheckIsPassBusinessRule2(sheet2, user, list,saleAgentInfo);
		}
		saleAgents.addAll(saleAgents1);
		/**添加更新代理信息**/
		Integer add=0;
		if(null!=updateInfo&&updateInfo.size()>0){
			//TODO
			agentInfoMtDao.updateBachAgentInfo(updateInfo);
			update=updateInfo.size();
		}
		if(null!=addInfo && addInfo.size()>0){
			agentInfoMtDao.addBatchAgentInfo(addInfo);
			add=addInfo.size();
		}
		/**代理信息方案关联处理*/
		if("5".equals(agentType)&&lastRowNumber>3){
			commPlan(sheet, commPlanUpdate, commPlanAll);
		}
		logger.info("插入信息代理信息: 添加 "+add+"更新"+update);
		session.setAttribute("result", "销售信息导入成功: 添加 "+add+"条  更新"+update+"条");
		/**添加广东销售信息*/
		if(null!=saleAgents&&saleAgents.size()>0){
			agentInfoMtDao.addTradeSale(saleAgents,agentType);
			if(add+update==0){
				session.setAttribute("result", "广东销售信息导入成功");
			}
		}
		/**
		 * 车险费用信息
		 */
		Map<String,List<TradAgentInfoRela>> saleAgents3=new HashMap<String,List<TradAgentInfoRela>>();
		if("4".equals(agentType)&&lastRow3>=1&&flag3){
			saleAgents3=importCarHandlingFee(sheet3, user);
			List<TradAgentInfoRela> addList = saleAgents3.get("add");
			List<TradAgentInfoRela> updateList = saleAgents3.get("update");
			if(null!=addList&&addList.size()>0){
				agentInfoRelaDao.insertIntoAgentRealForCar(addList);
			}
			if(null!=updateList&&updateList.size()>0){
				agentInfoRelaDao.updateAlreadyExistsCarInfo(updateList);
			}
			logger.info("插入车险费用信息  更新："+updateList.size()+" 新增："+addList.size());
		}
	}
	/**处理代理信息更新的情况**/
	private List<String>  saleListUpdate(List<TradAgentInfo>  agentInfos){
		List<String> agentInfoId=new ArrayList<String>();
		for (int i = 0; i < agentInfos.size(); i++) {
			if(agentInfos.get(i).getDptCde().startsWith("44")){
				agentInfoId.add(agentInfos.get(i).getId()+"");
			}
		}
		return agentInfoId;
	}
	/**联合出单方案关联处理*/
	private void commPlan(HSSFSheet sheet,List<String> commPlanUpdate, List<String> commPlanAll){
		try {
			List<String> partners=new ArrayList<String>();
			Map<String, Object> map=new HashMap<String, Object>();
			/**获取所有配置的合作伙伴*/
			HSSFRow row=sheet.getRow(2);
			if(row!=null){
				/**获取所有需要添加的合作伙伴*/
				for (int i = 0; i < row.getLastCellNum(); i++) {
					String parterner=ExcelUtil.getCellText(row.getCell(i)).trim();
					if(StrUtil.isNotEmpty(parterner)){
						partners.add(parterner);
					};
				}
				if(partners!=null&&partners.size()>0){
					map.put("partners", partners);
					/**删除所有已关联方案的关联信息*/
					if(commPlanUpdate!=null&&commPlanUpdate.size()>0){
						map.put("agentInfoId", commPlanUpdate);
						/**删除已关联的代理信息*/
						agentInfoMtDao.deleteCommPlanRate(map);
					}
					/**执行插入操作*/
					if(commPlanAll!=null&&commPlanAll.size()>0){
						map.put("keys", commPlanAll);
						agentInfoMtDao.addCommPlanRate(map);
					}
				}
			}
		} catch (Exception e) {
			logger.info("执行联合出单导入关联方案操作",e);
		}
	}
	/**总对总财险费用跟单*/
	private List<TradSaleAgent> importPreCheckSubBranchAgentTypeIsPassBusinessRule1(HSSFSheet sheet,GatewayUser user,List<TradAgentInfo> infos) throws AppException {
 		StringBuffer errorMsg = new StringBuffer();
		int errorCount = 0;
		userCodes.clear();
		countMap.clear();
		Map<String, List<TradSaleAgent>> map=new HashMap<String, List<TradSaleAgent>>();
		Map<String, String> dictMap=new HashMap<String, String>();
		List<DictDto> saleMenTypes=DictCache.getDict(Constants.DICT_SALE_MEN_TYPE);
		for (DictDto dictDto : saleMenTypes) {
			dictMap.put(dictDto.getCodeName(), dictDto.getCode());
		}
		List<DictDto> saleTypes=DictCache.getDict(Constants.DICT_SALES_TYPE);
		for (DictDto dictDto : saleTypes) {
			dictMap.put(dictDto.getCodeName(), dictDto.getCode());
		}
		List<TradSaleAgent> saleAgents=new ArrayList<TradSaleAgent>();
		String reg="^[0-9]+([.]{1}[0-9]+){0,1}$";
		//2、检查必填项必须不能为""串且符合编写格式；
		for (int rowNum = 3; rowNum <= sheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = sheet.getRow(rowNum);
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			// 1、首先筛选排除空白行或者不满足录入列条件的行数据
			if (hssfRow == null) {
				continue;
			}else if(9>hssfRow.getLastCellNum()){
				errorMsg.append("财险-广东销售信息第 "+(rowNum+1)+" 行数据未导入，列长度不满足业务需求规则，请检查数据或模板有更新请重新下载模板 <br>");
				errorCount++;
				continue;
			}
			//2、检查必填项必须不能为""串且符合编写格式；
			for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
				//空格检查
				if((0!=colNum)&&(3!=colNum)&&(7!=colNum) && (9!=colNum)&& (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){
					errorMsg.append("财险-广东销售信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据为必录项不允许为空，请检查 <br>");
					errorCount++;
					continue;
				}
				//第一列检查
				if((5==colNum)&&(!(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().matches(reg)))){
					errorMsg.append(" 财险-广东销售信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据格式不正确（注：正确格式只能录入数字,小数点），请检查 <br>");
					errorCount++;
					continue;
				}
			}
			TradSaleAgent saleAgent=new TradSaleAgent();
			saleAgent.setPracticeNo(ExcelUtil.getCellText(hssfRow.getCell(3)).trim());
			if(StrUtil.isEmpty(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(4)).trim()))){
				errorMsg.append(" 财险-广东销售信息第 "+(rowNum+1)+" 行，销售人员类型有误，请检查 <br>");
				errorCount++;
				continue;
			}
			saleAgent.setSaleType(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(4)).trim()));
			if((ExcelUtil.getCellText(hssfRow.getCell(5)).trim().matches(reg))){
				saleAgent.setAgentFee(ExcelUtil.getCellNumber(hssfRow.getCell(5))+"");
			}
			if(StrUtil.isEmpty(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(6)).trim()))){
				errorMsg.append(" 财险-广东销售信息第 "+(rowNum+1)+" 行，人员类别有误，请检查 <br>");
				errorCount++;
				continue;
			}
			saleAgent.setPersonType(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(6)).trim()));
			saleAgent.setSalePerson(ExcelUtil.getCellText(hssfRow.getCell(7)).trim());
			saleAgent.setEmpNo(ExcelUtil.getCellText(hssfRow.getCell(8)).trim());
			saleAgent.setAgentNo(ExcelUtil.getCellText(hssfRow.getCell(9)).trim());
			saleAgent.setBranch(ExcelUtil.getCellText(hssfRow.getCell(2)).trim());
			saleAgent.setZone(ExcelUtil.getCellText(hssfRow.getCell(0)).trim());
			saleAgent.setChanalCode(ExcelUtil.getCellText(hssfRow.getCell(1)).trim());
			saleAgent.setInsertDate(new Date());
			if(StrUtil.isEmpty(saleAgent.getZone())){
				saleAgent.setSubChanal(saleAgent.getBranch());
			}else{
				saleAgent.setSubChanal(saleAgent.getChanalCode().concat(saleAgent.getZone()).concat(saleAgent.getBranch()));
			}
			List<TradSaleAgent> agents= map.get(saleAgent.getSubChanal());
			if(agents!=null&&agents.size()>0){
				saleAgent.setSeqNo((agents.size()+1)+"");
				agents.add(saleAgent);
			}else{
				agents=new ArrayList<TradSaleAgent>();
				saleAgent.setSeqNo((1)+"");
				agents.add(saleAgent);
				userCodes.add(saleAgent.getSubChanal());
				map.put(saleAgent.getSubChanal(), agents);
			}
			saleAgent.setPlanType(nocar);
			saleAgents.add(saleAgent);
			//为了弹出界面简洁，限制输出10个错误；
			String fee=saleAgent.getAgentFee()+"";
			String person=saleAgent.getPersonType();
			if(!"1".equals(person)&&StrUtil.isNotEmpty(person)){
				if(fee!=null&&fee.matches(reg)&&Double.parseDouble(fee)>0){
					errorMsg.append("财险-广东销售信息 第 "+(rowNum+1)+" 行， 所有人员类别是业务员的比例之和是1，其他类别只能是0<br>");
					errorCount++;
					continue;
				}
			}
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		//为了弹出界面简洁，限制输出10个错误；
		if(errorCount>10){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		Map<String, String> map2=new HashMap<String, String>();
		try {
			map2 = agentInfoMtDao.queryTrade1(getUserCodes(userCodes));
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		String users="";
		if(infos!=null){
			for (TradAgentInfo info : infos) {
				if(info.getDptCde()!=null&&info.getDptCde().startsWith("44")){
					if(StrUtil.isEmpty(map2.get(info.getSubChannelCode()))){
						map2.put(info.getSubChannelCode(), "id");
					}
				}
				
			}
		}
		List<String> strs=new ArrayList<String>();
		for (TradSaleAgent tradSaleAgent : saleAgents) {
			if(StrUtil.isEmpty(tradSaleAgent.getSubChanal())){
				continue;
			}
			users=(String) map2.get(tradSaleAgent.getSubChanal());
			if(StrUtil.isEmpty(users)){
				errorMsg.append("财险-广东销售信息网点代码为+"+tradSaleAgent.getBranch()+" 对应的代理信息不存在或出单机构为非广东机构<br>");
				errorCount++;
				continue;
			}else{
				strs.add(users);
				if(!"id".equals(users)){
					tradSaleAgent.setId(Long.parseLong(users));
				}
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		if(StrUtil.isNotEmpty(errorMsg.toString())){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		/**格式化*/
		/*String agentids=getUserCodes(strs);*/
		String agentids="";//拼接条件
		int listSize = strs.size();
		int listCount = 0;
		if(listSize/300<=0){
			listCount = 1;
		}else if(listSize/300>=0){
			listCount = listSize/300;
		}
		List<Integer> checkDBCountLs1 = new ArrayList<Integer>(listCount);
		for(int i=1;i<=listCount;i++){
			checkDBCountLs1.add(Integer.valueOf(i*300));
		}
		for(int i=0;i<strs.size();i++){
			if(checkDBCountLs1.contains(i)){
				//每够300条查询一次
				try {
					dateMap=agentInfoMtDao.queryTrade2(agentids.substring(0,agentids.length()-1),nocar);
					countMap.putAll(dateMap);
				} catch (Exception e1) {
					logger.info("查询已配置的销售信息",e1);
				}
				agentids="";//清空
			}
			agentids += "'"+strs.get(i)+"',";
		}
		//追加最后一次不满300条
		if(StrUtil.isNotEmpty(agentids)){
			try {
				dateMap=agentInfoMtDao.queryTrade2(agentids.substring(0,agentids.length()-1),nocar);
				countMap.putAll(dateMap);
			} catch (Exception e1) {
				logger.info("查询已配置的销售信息",e1);
			}
			agentids="";//清空
		}
		
/*		if(StrUtil.isNotEmpty(agentids)){
			try {
				countMap=agentInfoMtDao.queryTrade2(agentids,nocar);
			} catch (Exception e1) {
				logger.info("查询已配置的销售信息",e1);
			}
		}*/
		/***
		 * 对每个用户进行检验  
		 */
		for (String key : map.keySet()) {
			Integer count=0;
			List<TradSaleAgent> list= map.get(key);
			if(list!=null&&list.size()>0){
				/**根据TradSaleAgent是否有id判断是否有已配置的代理信息*/
				if(list.get(0).getId()!=null&&StrUtil.isNotEmpty(countMap.get(list.get(0).getId()+""))){
					count=Integer.parseInt(countMap.get(list.get(0).getId()+""));
				}
				if((list.size()+count)>5){
					errorMsg.append("财险-广东销售信息单个代理人最多5个 网点代码为"+list.get(0).getBranch()+" 超出<br>");
					errorCount++;
					continue;
				}
				for (TradSaleAgent agent : list) {
					//为了弹出界面简洁，限制输出10个错误；
					if(errorCount>10){
						throw new AppException("上传失败:<br>"+errorMsg.toString());
					}
					agent.setSeqNo(count+Integer.parseInt(agent.getSeqNo())+"");
				}
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		if(StrUtil.isNotEmpty(errorMsg.toString())){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		if(StrUtil.isNotEmpty(errorMsg.toString())){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		return saleAgents;
	}
	/**总对总新意健险费用跟单*/
	private List<TradSaleAgent> importNlifeCheckSubBranchAgentTypeIsPassBusinessRule(HSSFSheet sheet,GatewayUser user,List<TradAgentInfo> infos,List<TradAgentInfo> saleAgentInfos ) throws AppException {
 		StringBuffer errorMsg = new StringBuffer();
		int errorCount = 0;
		userCodes.clear();
		countMap.clear();
		Map<String, List<TradSaleAgent>> map=new HashMap<String, List<TradSaleAgent>>();
		Map<String, String> dictMap=new HashMap<String, String>();
		List<DictDto> roleTypes=DictCache.getDict(Constants.DICT_ROLE_STYPE);
		for (DictDto dictDto : roleTypes) {
			dictMap.put(dictDto.getCodeName(), dictDto.getCode());
		}
		List<TradSaleAgent> saleAgents=new ArrayList<TradSaleAgent>();
		String reg="^[0-9]+([.]{1}[0-9]+){0,1}$";
		//2、检查必填项必须不能为""串且符合编写格式；
		for (int rowNum = 3; rowNum <= sheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = sheet.getRow(rowNum);
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			// 1、首先筛选排除空白行或者不满足录入列条件的行数据
			if (hssfRow == null) {
				continue;
			}else if(5>hssfRow.getLastCellNum()){
				errorMsg.append("新意健险-广东销售信息第 "+(rowNum+1)+" 行数据未导入，列长度不满足业务需求规则，请检查数据或模板有更新请重新下载模板 <br>");
				errorCount++;
				continue;
			}
			//2、检查必填项必须不能为""串且符合编写格式；
			for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
				//空格检查
				if((0!=colNum)&&(6!=colNum)&&(7!=colNum)&&(8!=colNum) && (9!=colNum)&& (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){
					errorMsg.append("新意健险-广东销售信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据为必录项不允许为空，请检查 <br>");
					errorCount++;
					continue;
				}
				/**费用比例只能是数字*/
				if((5==colNum)&&(!(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().matches(reg)))){
					errorMsg.append(" 新意健险-广东销售信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据格式不正确（注：正确格式只能录入数字,小数点），请检查 <br>");
					errorCount++;
					continue;
				}
			}
			TradSaleAgent saleAgent=new TradSaleAgent();
			if(StrUtil.isEmpty(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(3)).trim()))){
				errorMsg.append(" 新意健险-广东销售信息第 "+(rowNum+1)+" 行，销售人员类别有误，请检查 <br>");
				errorCount++;
				continue;
			}
			saleAgent.setPersonType(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(3)).trim()));
			saleAgent.setEmpNo(ExcelUtil.getCellText(hssfRow.getCell(4)).trim());
			if((ExcelUtil.getCellText(hssfRow.getCell(5)).trim().matches(reg))){
				saleAgent.setAgentFee(ExcelUtil.getCellNumber(hssfRow.getCell(5))+"");
			}
			saleAgent.setSaleName(ExcelUtil.getCellText(hssfRow.getCell(6)).trim());
			saleAgent.setPracticeNo(ExcelUtil.getCellText(hssfRow.getCell(7)).trim());
			saleAgent.setAgentCode(ExcelUtil.getCellText(hssfRow.getCell(8)).trim());
			saleAgent.setAgentName(ExcelUtil.getCellText(hssfRow.getCell(9)).trim());
			saleAgent.setBranch(ExcelUtil.getCellText(hssfRow.getCell(2)).trim());
			saleAgent.setZone(ExcelUtil.getCellText(hssfRow.getCell(0)).trim());
			saleAgent.setChanalCode(ExcelUtil.getCellText(hssfRow.getCell(1)).trim());
			saleAgent.setInsertDate(new Date());
			saleAgent.setPlanType(nlife);/**新意健险标志*/
			if(StrUtil.isEmpty(saleAgent.getZone())){
				saleAgent.setSubChanal(saleAgent.getBranch());
			}else{
				saleAgent.setSubChanal(saleAgent.getChanalCode().concat(saleAgent.getZone()).concat(saleAgent.getBranch()));
			}
			List<TradSaleAgent> agents= map.get(saleAgent.getSubChanal());
			if(agents!=null&&agents.size()>0){
				saleAgent.setSeqNo((agents.size()+1)+"");
				agents.add(saleAgent);
			}else{
				agents=new ArrayList<TradSaleAgent>();
				saleAgent.setSeqNo((1)+"");
				agents.add(saleAgent);
				userCodes.add(saleAgent.getSubChanal());
				map.put(saleAgent.getSubChanal(), agents);
			}
			saleAgents.add(saleAgent);
			/**分摊比例不能大于1*/
			String fee=saleAgent.getAgentFee()+"";
			String person=saleAgent.getPersonType();
			if(StrUtil.isNotEmpty(person)){
				if(fee!=null&&fee.matches(reg)&&Double.parseDouble(fee)>1){
					errorMsg.append("新意健险-广东销售信息 第 "+(rowNum+1)+" 行，人员分摊比例不能大于1<br>");
					errorCount++;
					continue;
				}
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		//为了弹出界面简洁，限制输出10个错误；
		if(errorCount>10){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		Map<String, String> map2=new HashMap<String, String>();
		try {
			map2 = agentInfoMtDao.queryTrade1(getUserCodes(userCodes));
		} catch (Exception e1) {
			e1.printStackTrace();
		}
		String users="";
		if(infos!=null){
			for (TradAgentInfo info : infos) {
				if(info.getDptCde()!=null&&info.getDptCde().startsWith("44"))
					if(StrUtil.isEmpty(map2.get(info.getSubChannelCode()))){
						map2.put(info.getSubChannelCode(), "id");
					}
			}
		}
		List<String> strs=new ArrayList<String>();
		for (TradSaleAgent tradSaleAgent : saleAgents) {
			if(StrUtil.isEmpty(tradSaleAgent.getSubChanal())){
				continue;
			}
			users=(String) map2.get(tradSaleAgent.getSubChanal());
			if(StrUtil.isEmpty(users)){
				errorMsg.append("新意健险-广东销售信息网点代码为+"+tradSaleAgent.getBranch()+" 对应的代理信息不存在或出单机构为非广东机构<br>");
				errorCount++;
				continue;
			}else{
				strs.add(users);
				if(!"id".equals(users)){
					tradSaleAgent.setId(Long.parseLong(users));
				}
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		if(StrUtil.isNotEmpty(errorMsg.toString())){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		/**格式化*/
		/*String agentids=getUserCodes(strs);*/
		
		String agentids="";//拼接条件
		int listSize = strs.size();
		int listCount = 0;
		if(listSize/300<=0){
			listCount = 1;
		}else if(listSize/300>=0){
			listCount = listSize/300;
		}
		List<Integer> checkDBCountLs1 = new ArrayList<Integer>(listCount);
		for(int i=1;i<=listCount;i++){
			checkDBCountLs1.add(Integer.valueOf(i*300));
		}
		for(int i=0;i<strs.size();i++){
			if(checkDBCountLs1.contains(i)){
				//每够300条查询一次
				try {
					dateMap=agentInfoMtDao.queryTrade2(agentids.substring(0,agentids.length()-1),nlife);
					countMap.putAll(dateMap);
				} catch (Exception e1) {
					logger.info("查询已配置的销售信息",e1);
				}
				agentids="";//清空
			}
			agentids += "'"+strs.get(i)+"',";
		}
		//追加最后一次不满300条
		if(StrUtil.isNotEmpty(agentids)){
			try {
				dateMap=agentInfoMtDao.queryTrade2(agentids.substring(0,agentids.length()-1),nlife);
				countMap.putAll(dateMap);
			} catch (Exception e1) {
				logger.info("查询已配置的销售信息",e1);
			}
			agentids="";//清空
		}
		
	/*	if(StrUtil.isNotEmpty(agentids)){
			try {
				//TODO
				countMap=agentInfoMtDao.queryTrade2(agentids,nlife);
			} catch (Exception e1) {
				logger.info("查询已配置的销售信息",e1);
			}
		}*/
		/***
		 * 对每个用户进行检验  
		 */
		for (String key : map.keySet()) {
			Integer count=0;
			List<TradSaleAgent> list= map.get(key);
			if(list!=null&&list.size()>0){
				/**根据TradSaleAgent是否有id判断是否有已配置的代理信息*/
				if(list.get(0).getId()!=null&&StrUtil.isNotEmpty(countMap.get(list.get(0).getId()+""))){
					count=Integer.parseInt(countMap.get(list.get(0).getId()+""));
				}
				if((list.size()+count)>5){
					errorMsg.append("财险-广东销售信息单个代理人最多5个 网点代码为"+list.get(0).getBranch()+" 超出<br>");
					errorCount++;
					continue;
				}
				for (TradSaleAgent agent : list) {
					//为了弹出界面简洁，限制输出10个错误；
					if(errorCount>10){
						throw new AppException("上传失败:<br>"+errorMsg.toString());
					}
					agent.setSeqNo(count+Integer.parseInt(agent.getSeqNo())+"");
				}
			}
			/**查看是否所有广东渠道信息都 关联销售**/
			if(saleAgentInfos!=null){
				for (TradAgentInfo saleAgentInfo : saleAgentInfos) {
					if(!saleAgentInfo.getDptCde().startsWith("44")){
						continue;
					}
					String salekey="";
					if(StrUtil.isEmpty(saleAgentInfo.getZone())){
						salekey=saleAgentInfo.getSubChannelCode();
					}else{
						salekey=saleAgentInfo.getChannelCode().concat(saleAgentInfo.getZone()).concat(saleAgentInfo.getBranch());
					}
					List<TradSaleAgent> agentInfo=map.get(salekey);
					if (agentInfo==null||agentInfo.size()==0) {
						errorMsg.append("新意健险-广东销售信息未配置网点名称为："+saleAgentInfo.getSubChannelCode()+"<br>");
						errorCount++;
					}
					//为了弹出界面简洁，限制输出10个错误；
					if(errorCount>10){
						throw new AppException("上传失败:<br>"+errorMsg.toString());
					}
				}
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		
		//TODO
		if(StrUtil.isNotEmpty(errorMsg.toString())){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		return saleAgents;
	}

	//检查sheet 是否满足业务规则校验,检查个人代理类型的  
	private List<TradAgentInfo> importPreCheckIsPassBusinessRule(HSSFSheet sheet,GatewayUser user) throws AppException {
 		StringBuffer errorMsg = new StringBuffer();
 		//当前登陆用户所属分公司两位机构代码
 		String departmentStr = user.getDepartmentStr().substring(1, 3);
		int errorCount = 0;
		List<TradAgentInfo> temLs = new ArrayList<TradAgentInfo>();
		for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = sheet.getRow(rowNum);
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			// 1、首先筛选排除空白行或者不满足录入列条件的行数据
			if (hssfRow == null) {
				continue;
			}else if(19>hssfRow.getLastCellNum()){
				errorMsg.append("个人销售渠道信息第 "+(rowNum+1)+" 行数据未导入，列长度不满足业务需求规则，请检查数据或模板有更新请重新下载模板 <br>");
				errorCount++;
				continue;
			}
			boolean flag=true;
			//2、检查必填项必须不能为""串且符合编写格式；
			String regexM = "[A-Za-z0-9_]+";
			for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
				//空格检查
				if((19!=colNum)&&(11!=colNum)&&(3!=colNum) && (9!=colNum) &&(10!=colNum) &&(16!=colNum)&&(13!=colNum) && (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){
					errorMsg.append("个人销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据为必录项不允许为空，请检查 <br>");
					errorCount++;
					continue;
				}
				//第一列检查
				if(0==colNum && (!(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().matches(regexM)))){
					errorMsg.append(" 个人销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据格式不正确（注：正确格式只能录入字母、数字、下划线组合的字符），请检查 <br>");
					errorCount++;
					continue;
				}
				//第二列数据格式检查
				if(8==colNum && ("19002".equals(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim())||"19003".equals(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){
					if(StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(9)).trim())||StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(10)).trim())||StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(11)).trim())){
						errorMsg.append("个人销售渠道信息第 "+(rowNum+1)+" 行，原业务来源为19002或19003时，第 (10/11/12) 列数据不能为空,请检查 <br>");
						errorCount++;
						continue;
					}
				}
				if(5==colNum||6==colNum||7==colNum){
					int length=0;
					if(5==colNum){
						length=1;
					}else if(6==colNum){
						length=3;
					}else{
						length=5;
					}
					if(!checkAgentInfo(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim(),length)){
						errorMsg.append(" 个人销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列");
						if(5==colNum){
							errorMsg.append("归属渠道信息无效，请核实 <br>");
						}else if(6==colNum){
							errorMsg.append("渠道类型信息无效，请核实 <br>");
						}else if(7==colNum){
							errorMsg.append("新业务来源信息无效，请核实 <br>");
						}			
						flag=false;
						errorCount++;
						continue;
					}
				}
				if(10==colNum&&ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().length()>13){
					errorMsg.append(" 个人销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列，代理人协议号长度不能超过13位，请核实 <br>");
				}
				if(14==colNum&&ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().length()>10){
					errorMsg.append(" 个人销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列，业务员代码长度不能超过10位，请核实 <br>");
				}
				if(17==colNum&&ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().length()>11){
					errorMsg.append(" 个人销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列，团队代码长度不能超过11位，请核实 <br>");
				}
			}
			if(flag){
				if(checkAgent(ExcelUtil.getCellText(hssfRow.getCell(5)).trim(),ExcelUtil.getCellText(hssfRow.getCell(6)).trim(),ExcelUtil.getCellText(hssfRow.getCell(7)).trim())){
					errorMsg.append("个人销售渠道信息第 "+(rowNum+1)+" 行，归属渠道，渠道类型，新业务来源信息不匹配，请检查 <br>");
					errorCount++;
					continue;
				}
			}
			//校验导入出单机构代码：是否为登陆用户所属分公司下渠道信息
			if(!"00".equals(departmentStr) && !departmentStr.equals(ExcelUtil.getCellText(hssfRow.getCell(4)).trim().substring(0,2))){
				errorMsg.append("个人销售渠道信息第 "+(rowNum+1)+" 行，出单机构代码"+ExcelUtil.getCellText(hssfRow.getCell(4)).trim()+"不存在或没有相关权限，请检查修改<br>");
				errorCount++;
				continue;
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			//取单元格值
			String subChannelCode ="";
			String cardApplicant ="";
			//String agentType = ExcelUtil.getCellText(hssfRow.getCell(0)).replaceAll("\\s*", "");
			String userCode = ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
			String userName = ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
			String channelCode = ExcelUtil.getCellText(hssfRow.getCell(2)).trim();
			//非必录特殊处理
			if(null!=hssfRow.getCell(3) && StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(3)).trim())){
				subChannelCode = ExcelUtil.getCellText(hssfRow.getCell(3)).trim();
			}
			String dptCde = ExcelUtil.getCellText(hssfRow.getCell(4)).trim();
			
			String channleCode = ExcelUtil.getCellText(hssfRow.getCell(5)).trim();
			String channleType = ExcelUtil.getCellText(hssfRow.getCell(6)).trim();
			String newBsnsType = ExcelUtil.getCellText(hssfRow.getCell(7)).trim();
			String categoryCode = ExcelUtil.getCellText(hssfRow.getCell(8)).trim();
			
			String agentNo = ExcelUtil.getCellText(hssfRow.getCell(9)).trim();
			String protocolNo = ExcelUtil.getCellText(hssfRow.getCell(10)).trim();
			String cbrkrname = ExcelUtil.getCellText(hssfRow.getCell(11)).trim();
			String serviceCode = ExcelUtil.getCellText(hssfRow.getCell(12)).trim();
			String serviceName = ExcelUtil.getCellText(hssfRow.getCell(13)).trim();
			String empNo = ExcelUtil.getCellText(hssfRow.getCell(14)).trim();
			String empName = ExcelUtil.getCellText(hssfRow.getCell(15)).trim();
			if(null!=hssfRow.getCell(16) && StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(16)).trim())){
				cardApplicant = ExcelUtil.getCellText(hssfRow.getCell(16)).trim();
			}
			String teamCode = ExcelUtil.getCellText(hssfRow.getCell(17)).trim();
			String teamName = ExcelUtil.getCellText(hssfRow.getCell(18)).trim();
			String coprcod = ExcelUtil.getCellText(hssfRow.getCell(19)).trim();

			TradAgentInfo temObj = new TradAgentInfo();
			temObj.setAgentType("3");//由于之前已经校验，可以set 固定值；
			temObj.setExcelRowIndex(rowNum+1);
			temObj.setUserCode(userCode);
			temObj.setUserName(userName);
			temObj.setChannelCode(channelCode);
			if(StrUtil.isNotEmpty(subChannelCode)){
				temObj.setSubChannelCode(subChannelCode);
			}
			temObj.setDptCde(dptCde);
			temObj.setChannleCode(channleCode);
			temObj.setChannleType(channleType);
			temObj.setNewBsnsType(newBsnsType);
			temObj.setCategoryCode(categoryCode);
			temObj.setAgentNo(agentNo);
			temObj.setProtocolNo(protocolNo);
			temObj.setServiceCode(serviceCode);
			temObj.setServiceName(serviceName);
			temObj.setEmpNo(empNo);
			temObj.setEmpName(empName);
			temObj.setCbrkrname(cbrkrname);
			temObj.setCoprcod(coprcod);
			if(StrUtil.isNotEmpty(cardApplicant)){
				temObj.setCardApplicant(cardApplicant);
			}
			temObj.setTeamCode(teamCode);
			temObj.setTeamName(teamName);
			//temObj.setOperTime(new Date());
			temObj.setOperUser(user.getUserid());
			//添加到List 中
			temLs.add(temObj);
		}
		//3、校验excel中的用户名是否存在重复
		int checkUserCodeErrorCount=0;
		for(int i = 0; i < temLs.size() - 1; i++){
			String checkUserCode = temLs.get(i).getUserCode();
			String channelCode = temLs.get(i).getChannelCode();
			if(checkUserCodeErrorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			for(int j = i + 1; j < temLs.size(); j++){
				if(checkUserCode.equals(temLs.get(j).getUserCode())&&channelCode.equals(temLs.get(j).getChannelCode())){
					errorMsg.append("个人销售渠道信息代理人用户名：同一合作伙伴下（第一列）第  "+(i+3)+" 行与第 "+(j+3)+" 行代理人用户名重复，请修改 <br>");
					checkUserCodeErrorCount++;
					continue;
				}
			}
		}
		if(StrUtil.isNotEmpty(errorMsg.toString())){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		return temLs;
	}
	/**个人代理费用跟单*/
	private List<TradSaleAgent> importPreCheckIsPassBusinessRule1(HSSFSheet sheet,GatewayUser user,List<TradAgentInfo> infos) throws AppException {
 		StringBuffer errorMsg = new StringBuffer();
		int errorCount = 0;
		userCodes.clear();
		countMap.clear();
		Map<String, String> dictMap=new HashMap<String, String>();
		List<DictDto> saleMenTypes=DictCache.getDict(Constants.DICT_SALE_MEN_TYPE);
		for (DictDto dictDto : saleMenTypes) {
			dictMap.put(dictDto.getCodeName(), dictDto.getCode());
		}
		List<DictDto> saleTypes=DictCache.getDict(Constants.DICT_SALES_TYPE);
		for (DictDto dictDto : saleTypes) {
			dictMap.put(dictDto.getCodeName(), dictDto.getCode());
		}
		Map<String, List<TradSaleAgent>> map=new HashMap<String, List<TradSaleAgent>>();
		List<TradSaleAgent> saleAgents=new ArrayList<TradSaleAgent>();
		//2、检查必填项必须不能为""串且符合编写格式；
		String reg="^[0-9]+([.]{1}[0-9]+){0,1}$";
		for (int rowNum = 3; rowNum <= sheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = sheet.getRow(rowNum);
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			// 1、首先筛选排除空白行或者不满足录入列条件的行数据
			if (hssfRow == null) {
				continue;
			}else if(hssfRow.getLastCellNum()<8){
				errorMsg.append("财险-广东销售信息第 "+(rowNum+1)+" 行数据未导入，列长度不满足业务需求规则，请检查数据或模板有更新请重新下载模板 <br>");
				errorCount++;
				continue;
			}
			//2、检查必填项必须不能为""串且符合编写格式；
			for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
				//空格检查
				if((1!=colNum)&&(2!=colNum)&&(6!=colNum) && (8!=colNum) && (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){
					errorMsg.append("财险-广东销售信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据为必录项不允许为空，请检查 <br>");
					errorCount++;
					continue;
				}
				//第一列检查
				if((4==colNum)&&(!(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().matches(reg)))){
					errorMsg.append(" 财险-广东销售信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据格式不正确（注：正确格式只能录入数字,小数点），请检查 <br>");
					errorCount++;
					continue;
				}
				//为了弹出界面简洁，限制输出10个错误；
				if(errorCount>10){
					throw new AppException("上传失败:<br>"+errorMsg.toString());
				}
			}
			TradSaleAgent saleAgent=new TradSaleAgent();
			saleAgent.setPracticeNo(ExcelUtil.getCellText(hssfRow.getCell(2)).trim());
			if(StrUtil.isEmpty(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(3)).trim()))){
				errorMsg.append(" 财险-广东销售信息第 "+(rowNum+1)+" 行 销售人员类型有误，请检查 <br>");
				errorCount++;
				continue;
			}
			saleAgent.setSaleType(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(3)).trim()));
			if((ExcelUtil.getCellText(hssfRow.getCell(4)).trim().matches(reg))){
				saleAgent.setAgentFee(ExcelUtil.getCellNumber(hssfRow.getCell(4))+"");

			}
			if(StrUtil.isEmpty(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(5)).trim()))){
				errorMsg.append(" 财险-广东销售信息第 "+(rowNum+1)+" 行 人员类型有误，请检查 <br>");
				errorCount++;
				continue;	
			}
			saleAgent.setPersonType(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(5)).trim()));
			saleAgent.setSalePerson(ExcelUtil.getCellText(hssfRow.getCell(6)).trim());
			saleAgent.setEmpNo(ExcelUtil.getCellText(hssfRow.getCell(7)).trim());
			saleAgent.setAgentNo(ExcelUtil.getCellText(hssfRow.getCell(8)).trim());
			saleAgent.setUserCode(ExcelUtil.getCellText(hssfRow.getCell(0)).trim());
			saleAgent.setInsertDate(new Date());
			List<TradSaleAgent> agents= map.get(ExcelUtil.getCellText(hssfRow.getCell(0)).trim());
			if(agents!=null&&agents.size()>0){
				saleAgent.setSeqNo((agents.size()+1)+"");
				agents.add(saleAgent);
			}else{
				agents=new ArrayList<TradSaleAgent>();
				saleAgent.setSeqNo((1)+"");
				agents.add(saleAgent);
				userCodes.add(ExcelUtil.getCellText(hssfRow.getCell(0)).trim());
				map.put(ExcelUtil.getCellText(hssfRow.getCell(0)).trim(), agents);
			}
			saleAgent.setPlanType(nocar);
			saleAgents.add(saleAgent);
			//为了弹出界面简洁，限制输出10个错误；
			String fee=saleAgent.getAgentFee()+"";
			String person=saleAgent.getPersonType();
			if(!"1".equals(person)&&StrUtil.isNotEmpty(person)){
				if(fee!=null&&fee.matches(reg)&&Double.parseDouble(fee)>0){
					errorMsg.append("财险-广东销售信息 第 "+(rowNum+1)+" 行，所有人员类别是业务员的比例之和是1，其他类别只能是0<br>");
					errorCount++;
					continue;
				}
			}

			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		Map<String, String> map2=new HashMap<String, String>();
		try {
			map2 = agentInfoMtDao.queryTrade(getUserCodes(userCodes));
		} catch (Exception e1) {
			logger.info("查询配置信息是否存在",e1);
		}
		String users="";
		/**导入的代理信息*/
		if(infos!=null){
			for (TradAgentInfo info : infos) {
				if(info.getDptCde()!=null&&info.getDptCde().startsWith("44")){
					if(StrUtil.isEmpty(map2.get(info.getUserCode()))){
						map2.put(info.getUserCode(), "id");
					}
				}
			}
		}
		List<String> strs=new ArrayList<String>();
		for (TradSaleAgent tradSaleAgent : saleAgents) {
			if(StrUtil.isEmpty(tradSaleAgent.getUserCode())){
				continue;
			}
			users=map2.get(tradSaleAgent.getUserCode());
			if(StrUtil.isEmpty(users)){
				errorMsg.append("财险-广东销售信息用户名为+"+tradSaleAgent.getUserCode()+" 对应的代理信息不存在或出单机构为非广东机构<br>");
				errorCount++;
				continue;
			}else{
				if(!"id".equals(users)){
					tradSaleAgent.setId(Long.parseLong(users));
				}
				strs.add(users);
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		/**格式化*/
	/*	String agentids=getUserCodes(strs);*/
		/**查询用户已配置的销售信息*/
		String agentids="";//拼接条件
		int listSize = strs.size();
		int listCount = 0;
		if(listSize/300<=0){
			listCount = 1;
		}else if(listSize/300>=0){
			listCount = listSize/300;
		}
		List<Integer> checkDBCountLs1 = new ArrayList<Integer>(listCount);
		for(int i=1;i<=listCount;i++){
			checkDBCountLs1.add(Integer.valueOf(i*300));
		}
		for(int i=0;i<strs.size();i++){
			if(checkDBCountLs1.contains(i)){
				//每够300条查询一次
				try {
					dateMap=agentInfoMtDao.queryTrade2(agentids.substring(0,agentids.length()-1),nocar);
					countMap.putAll(dateMap);
				} catch (Exception e1) {
					logger.info("查询已配置的销售信息",e1);
				}
				agentids="";//清空
			}
			agentids += "'"+strs.get(i)+"',";
		}
		//追加最后一次不满300条
		if(StrUtil.isNotEmpty(agentids)){
			try {
				dateMap=agentInfoMtDao.queryTrade2(agentids.substring(0,agentids.length()-1),nocar);
				countMap.putAll(dateMap);
			} catch (Exception e1) {
				logger.info("查询已配置的销售信息",e1);
			}
			agentids="";//清空
		}
/*		if(StrUtil.isNotEmpty(agentids)){
			try {
				countMap=agentInfoMtDao.queryTrade2(agentids,nocar);
			} catch (Exception e1) {
				logger.info("查询已配置的销售信息",e1);
			}
		}*/
		/***
		 * 对每个用户进行检验  
		 */
		for (String key : map.keySet()) {
			if(StrUtil.isEmpty(key)){
				continue;
			}
			Integer count=0;
			List<TradSaleAgent> list= map.get(key);
			if(list!=null&&list.size()>0){
				/**根据TradSaleAgent是否有id判断是否有已配置的代理信息*/
				if(list.get(0).getId()!=null&&StrUtil.isNotEmpty(countMap.get(list.get(0).getId()+""))){
					count=Integer.parseInt(countMap.get(list.get(0).getId()+""));
				}
				if((list.size()+count)>5){
					errorMsg.append("财险-广东销售信息每个用户最多5个 用户名为"+key+" 超出<br>");
					errorCount++;
				}
				for (TradSaleAgent agent : list) {
					//为了弹出界面简洁，限制输出10个错误；
					if(errorCount>10){
						throw new AppException("上传失败:<br>"+errorMsg.toString());
					}
					agent.setSeqNo(count+Integer.parseInt(agent.getSeqNo())+"");
				}
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		if(StrUtil.isNotEmpty(errorMsg.toString())){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		return saleAgents;
	}
	/**个人代理新意健险费用跟单*/
	/****
	 * @param sheet
	 * @param user
	 * @param infos
	 * @param saleAgentInfos广东代理渠道信息列表
	 * @return
	 * @throws AppException
	 */
	private List<TradSaleAgent> importNlifeCheckIsPassBusinessRule1(HSSFSheet sheet,GatewayUser user,List<TradAgentInfo> infos,List<TradAgentInfo> saleAgentInfos) throws AppException {
 		StringBuffer errorMsg = new StringBuffer();
		int errorCount = 0;
		userCodes.clear();
		countMap.clear();
		Map<String, String> dictMap=new HashMap<String, String>();
		List<DictDto> roleTypes=DictCache.getDict(Constants.DICT_ROLE_STYPE);
		for (DictDto dictDto : roleTypes) {
			dictMap.put(dictDto.getCodeName(), dictDto.getCode());
		}
		Map<String, List<TradSaleAgent>> map=new HashMap<String, List<TradSaleAgent>>();
		List<TradSaleAgent> saleAgents=new ArrayList<TradSaleAgent>();
		//2、检查必填项必须不能为""串且符合编写格式；
		String reg="^[0-9]+([.]{1}[0-9]+){0,1}$";
		for (int rowNum = 3; rowNum <= sheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = sheet.getRow(rowNum);
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			// 1、首先筛选排除空白行或者不满足录入列条件的行数据
			if (hssfRow == null) {
				continue;
			}else if(hssfRow.getLastCellNum()<4){
				errorMsg.append("新意健险-广东销售信息第 "+(rowNum+1)+" 行数据未导入，列长度不满足业务需求规则，请检查数据或模板有更新请重新下载模板 <br>");
				errorCount++;
				continue;
			}
			//2、检查必填项必须不能为""串且符合编写格式；
			for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
				//空格检查
				if((1!=colNum)&&(5!=colNum)&&(6!=colNum) && (7!=colNum)&&(8!=colNum) && (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){
					errorMsg.append("新意健险-广东销售信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据为必录项不允许为空，请检查 <br>");
					errorCount++;
					continue;
				}
				//手续费比例必须为数字
				if((4==colNum)&&(!(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().matches(reg)))){
					errorMsg.append(" 新意健险-广东销售信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据格式不正确（注：正确格式只能录入数字,小数点），请检查 <br>");
					errorCount++;
					continue;
				}
				//为了弹出界面简洁，限制输出10个错误；
				if(errorCount>10){
					throw new AppException("上传失败:<br>"+errorMsg.toString());
				}
			}
			TradSaleAgent saleAgent=new TradSaleAgent();
			if(StrUtil.isEmpty(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(2)).trim()))){
				errorMsg.append(" 新意健险-广东销售信息第 "+(rowNum+1)+" 行 销售人员类别有误，请检查 <br>");
				errorCount++;
				continue;
			}
			saleAgent.setPersonType(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(2)).trim()));
			saleAgent.setEmpNo(ExcelUtil.getCellText(hssfRow.getCell(3)).trim());

			if((ExcelUtil.getCellText(hssfRow.getCell(4)).trim().matches(reg))){
				saleAgent.setAgentFee(ExcelUtil.getCellNumber(hssfRow.getCell(4))+"");

			}
			saleAgent.setSaleName(ExcelUtil.getCellText(hssfRow.getCell(5)).trim());
			saleAgent.setPracticeNo(ExcelUtil.getCellText(hssfRow.getCell(6)).trim());
			saleAgent.setAgentCode(ExcelUtil.getCellText(hssfRow.getCell(7)).trim());
			saleAgent.setAgentName(ExcelUtil.getCellText(hssfRow.getCell(8)).trim());
			saleAgent.setUserCode(ExcelUtil.getCellText(hssfRow.getCell(0)).trim());
			saleAgent.setInsertDate(new Date());
			List<TradSaleAgent> agents= map.get(ExcelUtil.getCellText(hssfRow.getCell(0)).trim());
			if(agents!=null&&agents.size()>0){
				saleAgent.setSeqNo((agents.size()+1)+"");
				agents.add(saleAgent);
			}else{
				agents=new ArrayList<TradSaleAgent>();
				saleAgent.setSeqNo((1)+"");
				agents.add(saleAgent);
				userCodes.add(ExcelUtil.getCellText(hssfRow.getCell(0)).trim());
				map.put(ExcelUtil.getCellText(hssfRow.getCell(0)).trim(), agents);
			}
			saleAgent.setPlanType(nlife);
			saleAgents.add(saleAgent);
			/**分摊比例不能大于1*/
			String fee=saleAgent.getAgentFee()+"";
			String person=saleAgent.getPersonType();
			if(StrUtil.isNotEmpty(person)){
				if(fee!=null&&fee.matches(reg)&&Double.parseDouble(fee)>1){
					errorMsg.append("新意健险-广东销售信息 第 "+(rowNum+1)+" 行，人员分摊比例不能大于1<br>");
					errorCount++;
					continue;
				}
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		Map<String, String> map2=new HashMap<String, String>();
		try {
			map2 = agentInfoMtDao.queryTrade(getUserCodes(userCodes));
		} catch (Exception e1) {
			logger.info("查询配置信息是否存在",e1);
		}
		String users="";
		/**导入的代理信息*/
		if(infos!=null){
			for (TradAgentInfo info : infos) {
				if(info.getDptCde()!=null&&info.getDptCde().startsWith("44"))
					if(StrUtil.isEmpty(map2.get(info.getUserCode()))){
						map2.put(info.getUserCode(), "id");
					}
				}
		}
		List<String> strs=new ArrayList<String>();
		for (TradSaleAgent tradSaleAgent : saleAgents) {
			if(StrUtil.isEmpty(tradSaleAgent.getUserCode())){
				continue;
			}
			users=map2.get(tradSaleAgent.getUserCode());
			if(StrUtil.isEmpty(users)){
				errorMsg.append("新意健险-广东销售信息用户名为+"+tradSaleAgent.getUserCode()+" 对应的代理信息不存在或出单机构为非广东机构<br>");
				errorCount++;
				continue;
			}else{
				if(!"id".equals(users)){
					tradSaleAgent.setId(Long.parseLong(users));
				}
				strs.add(users);
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		/**格式化*/
		/*String agentids=getUserCodes(strs);*/
		/**查询用户已配置的销售信息*/
		
		String agentids="";//拼接条件
		int listSize = strs.size();
		int listCount = 0;
		if(listSize/300<=0){
			listCount = 1;
		}else if(listSize/300>=0){
			listCount = listSize/300;
		}
		List<Integer> checkDBCountLs1 = new ArrayList<Integer>(listCount);
		for(int i=1;i<=listCount;i++){
			checkDBCountLs1.add(Integer.valueOf(i*300));
		}
		for(int i=0;i<strs.size();i++){
			if(checkDBCountLs1.contains(i)){
				//每够300条查询一次
				try {
					dateMap=agentInfoMtDao.queryTrade2(agentids.substring(0,agentids.length()-1),nlife);
					countMap.putAll(dateMap);
				} catch (Exception e1) {
					logger.info("查询已配置的销售信息",e1);
				}
				agentids="";//清空
			}
			agentids += "'"+strs.get(i)+"',";
		}
		//追加最后一次不满300条
		if(StrUtil.isNotEmpty(agentids)){
			try {
				dateMap=agentInfoMtDao.queryTrade2(agentids.substring(0,agentids.length()-1),nlife);
				countMap.putAll(dateMap);
			} catch (Exception e1) {
				logger.info("查询已配置的销售信息",e1);
			}
			agentids="";//清空
		}
		
	/*	if(StrUtil.isNotEmpty(agentids)){
			try {
				countMap=agentInfoMtDao.queryTrade2(agentids,nlife);
			} catch (Exception e1) {
				logger.info("查询已配置的销售信息",e1);
			}
		}*/
		/***
		 * 对每个用户进行检验  
		 */
		for (String key : map.keySet()) {
			if(StrUtil.isEmpty(key)){
				continue;
			}
			Integer count=0;
			List<TradSaleAgent> list= map.get(key);
			if(list!=null&&list.size()>0){
				/**根据TradSaleAgent是否有id判断是否有已配置的代理信息*/
				if(list.get(0).getId()!=null&&StrUtil.isNotEmpty(countMap.get(list.get(0).getId()+""))){
					count=Integer.parseInt(countMap.get(list.get(0).getId()+""));
				}
				if((list.size()+count)>5){
					errorMsg.append("新意健险-广东销售信息每个用户最多5个 用户名为"+key+" 超出<br>");
					errorCount++;
				}
				for (TradSaleAgent agent : list) {
					//为了弹出界面简洁，限制输出10个错误；
					if(errorCount>10){
						throw new AppException("上传失败:<br>"+errorMsg.toString());
					}
					agent.setSeqNo(count+Integer.parseInt(agent.getSeqNo())+"");
				}
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		/**查看是否所有广东渠道信息都 关联销售**/
		if(saleAgentInfos!=null){
			for (TradAgentInfo saleAgentInfo : saleAgentInfos) {
				if(!saleAgentInfo.getDptCde().startsWith("44")){
					continue;
				}
				List<TradSaleAgent> agentInfo=map.get(saleAgentInfo.getUserCode());
				if (agentInfo==null||agentInfo.size()==0) {
					errorMsg.append("新意健险-广东销售信息未配置用户名为："+saleAgentInfo.getUserCode()+"<br>");
					errorCount++;
				}
				//为了弹出界面简洁，限制输出10个错误；
				if(errorCount>10){
					throw new AppException("上传失败:<br>"+errorMsg.toString());
				}
			}
		}
		if(StrUtil.isNotEmpty(errorMsg.toString())){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		return saleAgents;
	}
	/**
	 * 联合出单费用跟单财险
	 */
	private List<TradSaleAgent> importPreCheckIsPassBusinessRule2(HSSFSheet sheet,GatewayUser user,List<TradAgentInfo> infos) throws AppException {
 		StringBuffer errorMsg = new StringBuffer();
		int errorCount = 0;
		userCodes.clear();
		countMap.clear();
		Map<String, String> dictMap=new HashMap<String, String>();
		List<DictDto> saleMenTypes=DictCache.getDict(Constants.DICT_SALE_MEN_TYPE);
		for (DictDto dictDto : saleMenTypes) {
			dictMap.put(dictDto.getCodeName(), dictDto.getCode());
		}
		List<DictDto> saleTypes=DictCache.getDict(Constants.DICT_SALES_TYPE);
		for (DictDto dictDto : saleTypes) {
			dictMap.put(dictDto.getCodeName(), dictDto.getCode());
		}
		Map<String, List<TradSaleAgent>> map=new HashMap<String, List<TradSaleAgent>>();
		List<TradSaleAgent> saleAgents=new ArrayList<TradSaleAgent>();
		//2、检查必填项必须不能为""串且符合编写格式；
		String reg="^[0-9]+([.]{1}[0-9]+){0,1}$";
		for (int rowNum = 3; rowNum <= sheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = sheet.getRow(rowNum);
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			// 1、首先筛选排除空白行或者不满足录入列条件的行数据
			if (hssfRow == null) {
				continue;
			}else if(hssfRow.getLastCellNum()<10){
				errorMsg.append("财险-广东销售信息第 "+(rowNum+1)+" 行数据未导入，列长度不满足业务需求规则，请检查数据或模板有更新请重新下载模板 <br>");
				errorCount++;
				continue;
			}
			//2、检查必填项必须不能为""串且符合编写格式；
			for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
				//空格检查
				if((5!=colNum)&&(9!=colNum) && (11!=colNum) && (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){
					errorMsg.append("财险-广东销售信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据为必录项不允许为空，请检查 <br>");
					errorCount++;
					continue;
				}
				//第一列检查
				if((7==colNum)&&(!(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().matches(reg)))){
					errorMsg.append(" 财险-广东销售信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据格式不正确（注：正确格式只能录入数字,小数点），请检查 <br>");
					errorCount++;
					continue;
				}
				//为了弹出界面简洁，限制输出10个错误；
				if(errorCount>10){
					throw new AppException("上传失败:<br>"+errorMsg.toString());
				}
			}
			TradSaleAgent saleAgent=new TradSaleAgent();
			saleAgent.setPracticeNo(ExcelUtil.getCellText(hssfRow.getCell(5)).trim());
			if(StrUtil.isEmpty(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(6)).trim()))){
				errorMsg.append(" 财险-广东销售信息第 "+(rowNum+1)+" 行 销售人员类型有误，请检查 <br>");
				errorCount++;
				continue;
			}
			saleAgent.setSaleType(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(6)).trim()));
			if((ExcelUtil.getCellText(hssfRow.getCell(7)).trim().matches(reg))){
				saleAgent.setAgentFee(ExcelUtil.getCellNumber(hssfRow.getCell(7))+"");

			}
			if(StrUtil.isEmpty(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(8)).trim()))){
				errorMsg.append(" 财险-广东销售信息第 "+(rowNum+1)+" 行 人员类型有误，请检查 <br>");
				errorCount++;
				continue;	
			}
			saleAgent.setPersonType(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(8)).trim()));
			saleAgent.setSalePerson(ExcelUtil.getCellText(hssfRow.getCell(9)).trim());
			saleAgent.setEmpNo(ExcelUtil.getCellText(hssfRow.getCell(10)).trim());
			saleAgent.setAgentNo(ExcelUtil.getCellText(hssfRow.getCell(11)).trim());
			/*saleAgent.setUserCode(ExcelUtil.getCellText(hssfRow.getCell(0)).trim());*/
			saleAgent.setInsertDate(new Date());
			/**key值*/
			String key1=ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
			String key2=ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
			String key3=ExcelUtil.getCellText(hssfRow.getCell(2)).trim();
			String key4=ExcelUtil.getCellText(hssfRow.getCell(3)).trim();
			String key5=ExcelUtil.getCellText(hssfRow.getCell(4)).trim();

			//按同一条代理信息进行分组
			List<TradSaleAgent> agents= map.get(key1+"-"+key2+"-"+key3+"-"+key4+"-"+key5);
			if(agents!=null&&agents.size()>0){
				saleAgent.setSeqNo((agents.size()+1)+"");
				agents.add(saleAgent);
			}else{
				agents=new ArrayList<TradSaleAgent>();
				saleAgent.setSeqNo((1)+"");
				agents.add(saleAgent);
				userCodes.add(key1+"-"+key2+"-"+key3+"-"+key4+"-"+key5);
				map.put(key1+"-"+key2+"-"+key3+"-"+key4+"-"+key5, agents);
			}
			saleAgent.setKeys(key1+"-"+key2+"-"+key3+"-"+key4+"-"+key5);
			saleAgent.setPlanType(nocar);
			saleAgents.add(saleAgent);
			//为了弹出界面简洁，限制输出10个错误；
			String fee=saleAgent.getAgentFee()+"";
			String person=saleAgent.getPersonType();
			if(!"1".equals(person)&&StrUtil.isNotEmpty(person)){
				if(fee!=null&&fee.matches(reg)&&Double.parseDouble(fee)>0){
					errorMsg.append("财险-广东销售信息 第 "+(rowNum+1)+" 行，所有人员类别是业务员的比例之和是1，其他类别只能是0<br>");
					errorCount++;
					continue;
				}
			}

			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		Map<String, String> map2=new HashMap<String, String>();
		try {
			/**查询key值对应的代理信息**/
			map2 = agentInfoMtDao.queryTrade3(getUserCodes(userCodes));
		} catch (Exception e1) {
			logger.info("查询配置信息是否存在",e1);
		}
		String users="";
		/**导入的代理信息*/
		if(infos!=null){
			for (TradAgentInfo info : infos) {
				if(info.getDptCde()!=null&&info.getDptCde().startsWith("44"))
					if(StrUtil.isEmpty(map2.get(info.getKey1()+"-"+info.getKey2()+"-"+info.getKey3()+"-"+info.getKey4()+"-"+info.getKey5()))){
						map2.put(info.getKey1()+"-"+info.getKey2()+"-"+info.getKey3()+"-"+info.getKey4()+"-"+info.getKey5(), "id");
					}
				}
			}
		List<String> strs=new ArrayList<String>();
		for (TradSaleAgent tradSaleAgent : saleAgents) {
			if(StrUtil.isEmpty(tradSaleAgent.getKeys())){
				continue;
			}
			users=map2.get(tradSaleAgent.getKeys());
			/**代理信息不存在key值有误**/
			if(StrUtil.isEmpty(users)){
				errorMsg.append("财险-广东销售信息key值分别为+"+tradSaleAgent.getKeys()+" 对应的代理信息不存在或出单机构为非广东机构<br>");
				errorCount++;
				continue;
			}else{
				if(!"id".equals(users)){
					tradSaleAgent.setId(Long.parseLong(users));
				}
				strs.add(users);
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		/**格式化*/
		/*String agentids=getUserCodes(strs);*/
		/**查询用户已配置的销售信息*/
		
		String agentids="";//拼接条件
		int listSize = strs.size();
		int listCount = 0;
		if(listSize/300<=0){
			listCount = 1;
		}else if(listSize/300>=0){
			listCount = listSize/300;
		}
		List<Integer> checkDBCountLs1 = new ArrayList<Integer>(listCount);
		for(int i=1;i<=listCount;i++){
			checkDBCountLs1.add(Integer.valueOf(i*300));
		}
		for(int i=0;i<strs.size();i++){
			if(checkDBCountLs1.contains(i)){
				//每够300条查询一次
				try {
					dateMap=agentInfoMtDao.queryTrade2(agentids.substring(0,agentids.length()-1),nocar);
					countMap.putAll(dateMap);
				} catch (Exception e1) {
					logger.info("查询已配置的销售信息",e1);
				}
				agentids="";//清空
			}
			agentids += "'"+strs.get(i)+"',";
		}
		//追加最后一次不满300条
		if(StrUtil.isNotEmpty(agentids)){
			try {
				dateMap=agentInfoMtDao.queryTrade2(agentids.substring(0,agentids.length()-1),nocar);
				countMap.putAll(dateMap);
			} catch (Exception e1) {
				logger.info("查询已配置的销售信息",e1);
			}
			agentids="";//清空
		}
		
/*		if(StrUtil.isNotEmpty(agentids)){
			try {
				countMap=agentInfoMtDao.queryTrade2(agentids,nocar);
			} catch (Exception e1) {
				logger.info("查询已配置的销售信息",e1);
			}
		}*/
		/***
		 * 对每个用户进行检验  
		 */
		for (String key : map.keySet()) {
			if(StrUtil.isEmpty(key)){
				continue;
			}
			Integer count=0;
			List<TradSaleAgent> list= map.get(key);
			if(list!=null&&list.size()>0){
				/**根据TradSaleAgent是否有id判断是否有已配置的代理信息*/
				if(list.get(0).getId()!=null&&StrUtil.isNotEmpty(countMap.get(list.get(0).getId()+""))){
					count=Integer.parseInt(countMap.get(list.get(0).getId()+""));
				}
				if((list.size()+count)>5){
					errorMsg.append("财险-广东销售信息每个用户最多5个 key值为"+key+" 超出<br>");
					errorCount++;
				}
				for (TradSaleAgent agent : list) {
					//为了弹出界面简洁，限制输出10个错误；
					if(errorCount>10){
						throw new AppException("上传失败:<br>"+errorMsg.toString());
					}
					agent.setSeqNo(count+Integer.parseInt(agent.getSeqNo())+"");
				}
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		if(StrUtil.isNotEmpty(errorMsg.toString())){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		return saleAgents;
	}
	/**
	 * 联合出单费用跟单新意健险
	 */
	private List<TradSaleAgent> importNlifeCheckIsPassBusinessRule2(HSSFSheet sheet,GatewayUser user,List<TradAgentInfo> infos,List<TradAgentInfo> saleAgentInfos) throws AppException {
 		StringBuffer errorMsg = new StringBuffer();
		int errorCount = 0;
		userCodes.clear();
		countMap.clear();
		Map<String, String> dictMap=new HashMap<String, String>();
		List<DictDto> roleTypes=DictCache.getDict(Constants.DICT_ROLE_STYPE);
		for (DictDto dictDto : roleTypes) {
			dictMap.put(dictDto.getCodeName(), dictDto.getCode());
		}
		//TODO
		Map<String, List<TradSaleAgent>> map=new HashMap<String, List<TradSaleAgent>>();
		List<TradSaleAgent> saleAgents=new ArrayList<TradSaleAgent>();
		//2、检查必填项必须不能为""串且符合编写格式；
		String reg="^[0-9]+([.]{1}[0-9]+){0,1}$";
		for (int rowNum = 3; rowNum <= sheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = sheet.getRow(rowNum);
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			// 1、首先筛选排除空白行或者不满足录入列条件的行数据
			if (hssfRow == null) {
				continue;
			}else if(hssfRow.getLastCellNum()<7){
				errorMsg.append("新意健险-广东销售信息第 "+(rowNum+1)+" 行数据未导入，列长度不满足业务需求规则，请检查数据或模板有更新请重新下载模板 <br>");
				errorCount++;
				continue;
			}
			//2、检查必填项必须不能为""串且符合编写格式；
			for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
				//空格检查
				if((8!=colNum)&&(9!=colNum)&& (10!=colNum) && (11!=colNum) && (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){
					errorMsg.append("新意健险-广东销售信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据为必录项不允许为空，请检查 <br>");
					errorCount++;
					continue;
				}
				//第一列检查
				if((7==colNum)&&(!(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().matches(reg)))){
					errorMsg.append(" 新意健险-广东销售信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据格式不正确（注：正确格式只能录入数字,小数点），请检查 <br>");
					errorCount++;
					continue;
				}
				//为了弹出界面简洁，限制输出10个错误；
				if(errorCount>10){
					throw new AppException("上传失败:<br>"+errorMsg.toString());
				}
			}
			TradSaleAgent saleAgent=new TradSaleAgent();
			if(StrUtil.isEmpty(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(5)).trim()))){
				errorMsg.append(" 新意健险-广东销售信息第 "+(rowNum+1)+" 行 销售人员类型有误，请检查 <br>");
				errorCount++;
				continue;
			}
			saleAgent.setPersonType(dictMap.get(ExcelUtil.getCellText(hssfRow.getCell(5)).trim()));
			saleAgent.setEmpNo(ExcelUtil.getCellText(hssfRow.getCell(6)));
			if((ExcelUtil.getCellText(hssfRow.getCell(7)).trim().matches(reg))){
				saleAgent.setAgentFee(ExcelUtil.getCellText(hssfRow.getCell(7)));

			}
			saleAgent.setSaleName(ExcelUtil.getCellText(hssfRow.getCell(8)).trim());
			saleAgent.setPracticeNo(ExcelUtil.getCellText(hssfRow.getCell(9)).trim());
			saleAgent.setAgentCode(ExcelUtil.getCellText(hssfRow.getCell(10)).trim());
			saleAgent.setAgentName(ExcelUtil.getCellText(hssfRow.getCell(11)).trim());
			saleAgent.setInsertDate(new Date());
			/**key值*/
			String key1=ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
			String key2=ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
			String key3=ExcelUtil.getCellText(hssfRow.getCell(2)).trim();
			String key4=ExcelUtil.getCellText(hssfRow.getCell(3)).trim();
			String key5=ExcelUtil.getCellText(hssfRow.getCell(4)).trim();

			//按同一条代理信息进行分组
			List<TradSaleAgent> agents= map.get(key1+"-"+key2+"-"+key3+"-"+key4+"-"+key5);
			if(agents!=null&&agents.size()>0){
				saleAgent.setSeqNo((agents.size()+1)+"");
				agents.add(saleAgent);
			}else{
				agents=new ArrayList<TradSaleAgent>();
				saleAgent.setSeqNo((1)+"");
				agents.add(saleAgent);
				userCodes.add(key1+"-"+key2+"-"+key3+"-"+key4+"-"+key5);
				map.put(key1+"-"+key2+"-"+key3+"-"+key4+"-"+key5, agents);
			}
			saleAgent.setKeys(key1+"-"+key2+"-"+key3+"-"+key4+"-"+key5);
			saleAgent.setPlanType(nlife);
			saleAgents.add(saleAgent);
			/**分摊比例不能大于1*/
			String fee=saleAgent.getAgentFee()+"";
			String person=saleAgent.getPersonType();
			if(StrUtil.isNotEmpty(person)){
				if(fee!=null&&fee.matches(reg)&&Double.parseDouble(fee)>1){
					errorMsg.append("新意健险-广东销售信息 第 "+(rowNum+1)+" 行，人员分摊比例不能大于1<br>");
					errorCount++;
					continue;
				}
			}
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		Map<String, String> map2=new HashMap<String, String>();
		try {
			/**查询key值对应的代理信息**/
			map2 = agentInfoMtDao.queryTrade3(getUserCodes(userCodes));
		} catch (Exception e1) {
			logger.info("查询配置信息是否存在",e1);
		}
		String users="";
		/**导入的代理信息*/
		if(infos!=null){
			for (TradAgentInfo info : infos) {
				if(info.getDptCde()!=null&&info.getDptCde().startsWith("44"))
					if(StrUtil.isEmpty(map2.get(info.getKey1()+"-"+info.getKey2()+"-"+info.getKey3()+"-"+info.getKey4()+"-"+info.getKey5()))){
						map2.put(info.getKey1()+"-"+info.getKey2()+"-"+info.getKey3()+"-"+info.getKey4()+"-"+info.getKey5(), "id");
					}			
			 }
		}
		List<String> strs=new ArrayList<String>();
		for (TradSaleAgent tradSaleAgent : saleAgents) {
			if(StrUtil.isEmpty(tradSaleAgent.getKeys())){
				continue;
			}
			users=map2.get(tradSaleAgent.getKeys());
			/**代理信息不存在key值有误**/
			if(StrUtil.isEmpty(users)){
				errorMsg.append("新意健险-广东销售信息key值分别为+"+tradSaleAgent.getKeys()+" 对应的代理信息不存在或出单机构为非广东机构<br>");
				errorCount++;
				continue;
			}else{
				if(!"id".equals(users)){
					tradSaleAgent.setId(Long.parseLong(users));
				}
				strs.add(users);
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		/**格式化*/
	/*	String agentids=getUserCodes(strs);*/
		/**查询用户已配置的销售信息*/
		String agentids="";//拼接条件
		int listSize = strs.size();
		int listCount = 0;
		if(listSize/300<=0){
			listCount = 1;
		}else if(listSize/300>=0){
			listCount = listSize/300;
		}
		List<Integer> checkDBCountLs1 = new ArrayList<Integer>(listCount);
		for(int i=0;i<=listCount;i++){
			checkDBCountLs1.add(Integer.valueOf(i*300));
		}
		for(int i=0;i<strs.size();i++){
			if(checkDBCountLs1.contains(i)){
				//每够300条查询一次
				try {
					dateMap=agentInfoMtDao.queryTrade2(agentids.substring(0,agentids.length()-1),nlife);
					countMap.putAll(dateMap);
				} catch (Exception e1) {
					logger.info("查询已配置的销售信息",e1);
				}
				agentids="";//清空
			}
			agentids += "'"+strs.get(i)+"',";
		}
		//追加最后一次不满300条
		if(StrUtil.isNotEmpty(agentids)){
			try {
				dateMap=agentInfoMtDao.queryTrade2(agentids.substring(0,agentids.length()-1),nlife);
				countMap.putAll(dateMap);
			} catch (Exception e1) {
				logger.info("查询已配置的销售信息",e1);
			}
			agentids="";//清空
		}
/*		if(StrUtil.isNotEmpty(agentids)){
			try {
				countMap=agentInfoMtDao.queryTrade2(agentids,nlife);
			} catch (Exception e1) {
				logger.info("查询已配置的销售信息",e1);
			}
		}*/
		/***
		 * 对每个用户进行检验  
		 */
		for (String key : map.keySet()) {
			if(StrUtil.isEmpty(key)){
				continue;
			}
			Integer count=0;
			List<TradSaleAgent> list= map.get(key);
			if(list!=null&&list.size()>0){
				/**根据TradSaleAgent是否有id判断是否有已配置的代理信息*/
				if(list.get(0).getId()!=null&&StrUtil.isNotEmpty(countMap.get(list.get(0).getId()+""))){
					count=Integer.parseInt(countMap.get(list.get(0).getId()+""));
				}
				if((list.size()+count)>5){
					errorMsg.append("财险-广东销售信息每个用户最多5个 key值为"+key+" 超出<br>");
					errorCount++;
				}
				for (TradSaleAgent agent : list) {
					//为了弹出界面简洁，限制输出10个错误；
					if(errorCount>10){
						throw new AppException("上传失败:<br>"+errorMsg.toString());
					}
					agent.setSeqNo(count+Integer.parseInt(agent.getSeqNo())+"");
				}
			}
 			/**查看是否所有广东渠道信息都 关联销售**/
			if(saleAgentInfos!=null){
				for (TradAgentInfo saleAgentInfo : saleAgentInfos) {
					if(!saleAgentInfo.getDptCde().startsWith("44")){
						continue;
					}
/*					.getKey1()+"-"+saleAgentInfo.getKey2()+"-"+saleAgentInfo.getKey3()+"-"+saleAgentInfo.getKey4()+"-"+saleAgentInfo.getKey5();
*/					StringBuffer salekey= new StringBuffer("");
					salekey.append(saleAgentInfo.getKey1()).append("-").append(saleAgentInfo.getKey2()).append("-").append(saleAgentInfo.getKey3()).append("-").append(saleAgentInfo.getKey4()).append("-").append(saleAgentInfo.getKey5());
					List<TradSaleAgent> agentInfo=map.get(salekey.toString());
					if (agentInfo==null||agentInfo.size()==0) {
						errorMsg.append("新意健险-广东销售信息未配置key值分别为为："+salekey.toString()+"<br>");
						errorCount++;
					}
					//为了弹出界面简洁，限制输出10个错误；
					if(errorCount>10){
						throw new AppException("上传失败:<br>"+errorMsg.toString());
					}
				}
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
		}
		if(StrUtil.isNotEmpty(errorMsg.toString())){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		return saleAgents;
	}

	/**格式化用户代码**/
	private  String getUserCodes(List<String> userCodes){
		String ids="";
		if(userCodes!=null&&userCodes.size()>0){
			for (int i = 0; i < userCodes.size(); i++) {
				if(i==0){
					ids=ids+"'"+userCodes.get(i)+"'";
				}else{
					ids=ids+",'"+userCodes.get(i)+"'";
				}
			}
		}
		return ids;
	}
	
	/**
	 * 检查sheet 是否满足业务规则校验,检查联合出单代理类型
	 */
	private List<TradAgentInfo> CheckCombinedSubBranchAgentType(HSSFSheet sheet,GatewayUser user) throws AppException {
		StringBuffer errorMsg = new StringBuffer();
		int errorCount = 0;
		List<TradAgentInfo> temLs = new ArrayList<TradAgentInfo>();
		//当前登陆用户所属分公司两位机构代码
 		String departmentStr = user.getDepartmentStr().substring(1, 3);
		for (int rowNum = 4; rowNum <= sheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = sheet.getRow(rowNum);
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			boolean flag=true;
			// 1、首先筛选排除空白行或者不满足录入列条件的行数据
			if (hssfRow == null) {
				continue;
			}else if(22<hssfRow.getLastCellNum() || hssfRow.getLastCellNum()<19){
				errorMsg.append("第 "+(rowNum+1)+" 行数据未导入，原因：列长度不满足业务需求规则，请检查数据或模板有更新请重新下载模板 <br>");
				errorCount++;
				continue;
			}
			for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
				//空格检查 非空
				if((6!=colNum)&&(7!=colNum)&&(8!=colNum) && (10!=colNum) && (13!=colNum) && (16!=colNum) && (17!=colNum)&& (18!=colNum)&& (19!=colNum)&& (20!=colNum)&& (21!=colNum) && (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){
					errorMsg.append("第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据为必录项不允许为空，请检查 <br>");
					errorCount++;
					continue;
				}
				if(2==colNum||3==colNum||4==colNum){
					int length=0;
					if(2==colNum){
						length=1;
					}else if(3==colNum){
						length=3;
					}else{
						length=5;
					}
					if(!checkAgentInfo(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim(),length)){
						errorMsg.append(" 总对总销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列");
						if(2==colNum){
							errorMsg.append("归属渠道信息无效，请核实 <br>");
						}else if(3==colNum){
							errorMsg.append("渠道类型信息无效，请核实 <br>");
						}else if(4==colNum){
							errorMsg.append("新业务来源信息无效，请核实 <br>");
						}		
						flag=false;
						errorCount++;
						continue;
					}
				}
				if(7==colNum&&ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().length()>13){
					errorMsg.append(" 个人销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列，代理人协议号长度不能超过13位，请核实 <br>");
				}
				if(11==colNum&&ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().length()>10){
					errorMsg.append(" 个人销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列，业务员代码长度不能超过10位，请核实 <br>");
				}
				if(14==colNum&&ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().length()>11){
					errorMsg.append(" 个人销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列，团队代码长度不能超过11位，请核实 <br>");
				}
			}
			if(flag){
				if(checkAgent(ExcelUtil.getCellText(hssfRow.getCell(2)).trim(),ExcelUtil.getCellText(hssfRow.getCell(3)).trim(),ExcelUtil.getCellText(hssfRow.getCell(4)).trim())){
					errorMsg.append("总对总销售渠道信息第 "+(rowNum+1)+" 行，归属渠道，渠道类型，新业务来源信息不匹配，请检查 <br>");
					errorCount++;
					continue;
				}
			}
			//校验导入出单机构代码：是否为登陆用户所属分公司下渠道信息
			if(!"00".equals(departmentStr) && !departmentStr.equals(ExcelUtil.getCellText(hssfRow.getCell(1)).trim().substring(0,2))){
				errorMsg.append("总对总销售渠道信息第 "+(rowNum+1)+" 行，出单机构代码"+ExcelUtil.getCellText(hssfRow.getCell(1)).trim()+"不存在或没有相关权限，请检查修改<br>");
				errorCount++;
				continue;
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			//取单元格值
			String unionType = ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
			String dptCde = ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
			String channleCode = ExcelUtil.getCellText(hssfRow.getCell(2)).trim();
			String channleType = ExcelUtil.getCellText(hssfRow.getCell(3)).trim();
			String newBsnsType = ExcelUtil.getCellText(hssfRow.getCell(4)).trim();
			String categoryCode = ExcelUtil.getCellText(hssfRow.getCell(5)).trim();
			String agentNo = ExcelUtil.getCellText(hssfRow.getCell(6)).trim();
			String protocolNo = ExcelUtil.getCellText(hssfRow.getCell(7)).trim();
			String cbrkrname = ExcelUtil.getCellText(hssfRow.getCell(8)).trim();
			String serviceCode = ExcelUtil.getCellText(hssfRow.getCell(9)).trim();
			String serviceName = ExcelUtil.getCellText(hssfRow.getCell(10)).trim();
			String empNo = ExcelUtil.getCellText(hssfRow.getCell(11)).trim();
			String empName = ExcelUtil.getCellText(hssfRow.getCell(12)).trim();
			String cardApplicant = ExcelUtil.getCellText(hssfRow.getCell(13)).trim();
			String teamCode = ExcelUtil.getCellText(hssfRow.getCell(14)).trim();
			String teamName = ExcelUtil.getCellText(hssfRow.getCell(15)).trim();
			String coprcod = ExcelUtil.getCellText(hssfRow.getCell(16)).trim();
			String key1 = ExcelUtil.getCellText(hssfRow.getCell(17)).trim();
			String key2 = ExcelUtil.getCellText(hssfRow.getCell(18)).trim();
			String key3 = ExcelUtil.getCellText(hssfRow.getCell(19)).trim();
			String key4 = ExcelUtil.getCellText(hssfRow.getCell(20)).trim();
			String key5 = ExcelUtil.getCellText(hssfRow.getCell(21)).trim();
			TradAgentInfo temObj = new TradAgentInfo();
			temObj.setAgentType("5");
			temObj.setChannleCode(channleCode);
			temObj.setDptCde(dptCde);
			temObj.setUnionType(unionType);
			temObj.setChannleType(channleType);
			temObj.setNewBsnsType(newBsnsType);
			temObj.setCategoryCode(categoryCode);
			temObj.setAgentNo(agentNo);
			temObj.setProtocolNo(protocolNo);
			temObj.setCbrkrname(cbrkrname);
			temObj.setServiceCode(serviceCode);
			temObj.setServiceName(serviceName);
			temObj.setEmpNo(empNo);
			temObj.setEmpName(empName);
			temObj.setCardApplicant(cardApplicant);
			temObj.setTeamCode(teamCode);
			temObj.setTeamName(teamName);
			temObj.setCoprcod(coprcod);
			temObj.setKey1(key1);
			temObj.setKey2(key2);
			temObj.setKey3(key3);
			temObj.setKey4(key4);
			temObj.setKey5(key5);
			temLs.add(temObj);
		}
		//3、校验excel中的key值
		if(temLs.size()>0){
			int counterr = 0;
			for (int i = 0; i < temLs.size(); i++) {
				if(counterr >= 9){
					throw new AppException("上传失败:<br>"+errorMsg.toString());
				}
				String key1 = temLs.get(i).getKey1();
				String key2 = temLs.get(i).getKey2();
				String key3 = temLs.get(i).getKey3();
				String key4 = temLs.get(i).getKey4();
				String key5 = temLs.get(i).getKey5();
				//校验excel中key1~key5校验
				boolean primeKey = true;
				
				//为了弹出界面简洁，每行最多提示三个校验错误信息；
				int keyError =0 ;
				
				if(null == key1 || "".equals(key1)){
					errorMsg.append("联合出单销售渠道信息第"+ (i+3) +"行key1值为空，");
					counterr ++ ; 
					keyError ++ ;
					primeKey = false;
				}
				if(keyError< 3 && (null == key2 || "".equals(key2))){
					if(!primeKey){
						errorMsg.append("联合出单销售渠道信息key2值为空，");
						keyError ++;
					}else{
						errorMsg.append("联合出单销售渠道信息第"+ (i+3) +"行key2值为空，");
						counterr ++;
					}
					primeKey = false;
				}
				//校验excel 提示key3和key4值不能为空
				boolean primeKeyAmity = true;
				if(keyError< 3 && (null == key3 || "".equals(key3)) && (null == key4 || "".equals(key4)) && (null != key5 && !"".equals(key5))){
					if(!primeKey){
						errorMsg.append("联合出单销售渠道信息key3值和key4值为空，");
						keyError ++;
					}else{
						errorMsg.append("联合出单销售渠道信息第"+ (i+3) +"行key3值和key4值为空，");
						counterr ++;
					}
					primeKeyAmity = false;
					primeKey = false;
				}
				if(primeKeyAmity){
					if(keyError< 3 && (null == key3 || "".equals(key3)) && (null != key4 && !"" .equals(key4))){
						if(!primeKey){
							errorMsg.append("key3值为空，");
							keyError ++;
						}else{
							errorMsg.append("联合出单销售渠道信息第"+ (i+3) +"行key3值为空，");
							counterr ++;
						}
						primeKey = false;
					}
					if(keyError< 3 && (null == key4 || "".equals(key4)) && (null != key5 && !"" .equals(key5))){
						if(!primeKey){
							errorMsg.append("key4值为空,");
						}else{
							errorMsg.append("联合出单销售渠道信息第"+ (i+3) +"行key4值为空，");
							counterr ++;
						}
						primeKey = false;
					}
				}
				if(!primeKey){
					errorMsg.append("请检查<br/>");
				}
				//为了弹出界面简洁，限制输出10个错误；
				if(counterr > 9){
					throw new AppException("上传失败:<br>"+errorMsg.toString());
				}
				//当前行key值不符合要求，进行下一行校验
				if(!primeKey) continue;
				
				//校验excel中的key值不能重复
				//当前行key值
				StringBuffer sb1 = new StringBuffer();
				sb1.append(key1)
					.append(key2)
					.append(key3)
					.append(key4)
					.append(key5);
				for (int j = i+1; j < temLs.size(); j++) {
					//下一行key值（temLs[i+1]—(temLs.size()-1))
					StringBuffer sb2 = new StringBuffer();
					sb2.append(temLs.get(j).getKey1())
						.append(temLs.get(j).getKey2())
						.append(temLs.get(j).getKey3())
						.append(temLs.get(j).getKey4())
						.append(temLs.get(j).getKey5());
					if((sb1.toString()).equals(sb2.toString())){
						
						errorMsg.append("联合出单销售渠道信息第 "+(i+5)+" 行与第 "+(j+5)+" 行唯一值Key数据重复，请检查 <br>");
						counterr ++;
					}
					//为了弹出界面简洁，限制输出10个错误；
					if(counterr > 9){
						throw new AppException("上传失败:<br>"+errorMsg.toString());
					}
				}
			}
		}
		if(StrUtil.isNotEmpty(errorMsg.toString())){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		return temLs;
	}
	/*
	 * 检查sheet 是否满足业务规则校验,检查总对总代理类型
	 * 由于江苏邮政需求支持总对总界面出单改造
	 */
	private List<TradAgentInfo> importPreCheckSubBranchAgentTypeIsPassBusinessRule(HSSFSheet sheet,GatewayUser user) throws AppException {
 		StringBuffer errorMsg = new StringBuffer();
		int errorCount = 0;
		List<TradAgentInfo> temLs = new ArrayList<TradAgentInfo>();
		//当前登陆用户所属分公司两位机构代码
 		String departmentStr = user.getDepartmentStr().substring(1, 3);
		for (int rowNum = 2; rowNum <= sheet.getLastRowNum(); rowNum++) {
			HSSFRow hssfRow = sheet.getRow(rowNum);
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			boolean flag=true;
			// 1、首先筛选排除空白行或者不满足录入列条件的行数据
			if (hssfRow == null) {
				continue;
			}else if(21>hssfRow.getLastCellNum()){
				errorMsg.append("总对总销售渠道信息第 "+(rowNum+1)+" 行数据未导入，原因：列长度不满足业务需求规则，请检查数据或模板有更新请重新下载模板 <br>");
				errorCount++;
				continue;
			}
			Integer[] colNums={1,2,5,6,7,8,9,10,14,16,17,22,23};
			for (int colNum = 0; colNum < hssfRow.getLastCellNum(); colNum++) {
				//空格检查
			//	if((13!=colNum)&&(0!=colNum) && (3!=colNum) && (4!=colNum) && (11!=colNum) && (12!=colNum) && (18!=colNum) && (17!=colNum)&& (15!=colNum) &&(19!=colNum) &&(20!=colNum)&&(21!=colNum)&& (22!=colNum)&& (23!=colNum) && (24!=colNum)&& (25!=colNum)&& (26!=colNum)&& (27!=colNum)&& (28!=colNum)&& (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){
				if(Arrays.asList(colNums).contains(colNum)&& (StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){

					errorMsg.append("总对总销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据为必录项不允许为空，请检查 <br>");
					errorCount++;
					continue;
				//第一列检查
				}/*else if(0==colNum && !("总对总代理".equals(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){
					errorMsg.append("第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列数据只能录入：总对总代理，请检查 <br>");
					errorCount++;
					continue;
				}*/
				//第二列数据格式检查
				if(10==colNum && ("19002".equals(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim())||"19003".equals(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim()))){
					if(StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(11)).trim())||StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(12)).trim())||StrUtil.isEmpty(ExcelUtil.getCellText(hssfRow.getCell(13)).trim())){
						errorMsg.append("总对总销售渠道信息第 "+(rowNum+1)+" 行，原业务来源为19002或19003时，第 (12/13/14) 列数据不能为空,请检查 <br>");
						errorCount++;
						continue;
					}
				}
				if(7==colNum||8==colNum||9==colNum){
					int length=0;
					if(7==colNum){
						length=1;
					}else if(8==colNum){
						length=3;
					}else{
						length=5;
					}
					if(!checkAgentInfo(ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim(),length)){
						errorMsg.append(" 总对总销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列");
						if(7==colNum){
							errorMsg.append("归属渠道信息无效，请核实 <br>");
						}else if(8==colNum){
							errorMsg.append("渠道类型信息无效，请核实 <br>");
						}else if(9==colNum){
							errorMsg.append("新业务来源信息无效，请核实 <br>");
						}		
						flag=false;
						errorCount++;
						continue;
					}
				}
				if(12==colNum&&ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().length()>13){
					errorMsg.append(" 个人销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列，代理人协议号长度不能超过13位，请核实 <br>");
				}
				if(16==colNum&&ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().length()>10){
					errorMsg.append(" 个人销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列，业务员代码长度不能超过10位，请核实 <br>");
				}
				if(22==colNum&&ExcelUtil.getCellText(hssfRow.getCell(colNum)).trim().length()>11){
					errorMsg.append(" 个人销售渠道信息第 "+(rowNum+1)+" 行，第 "+(colNum+1)+" 列，团队代码长度不能超过11位，请核实 <br>");
				}
			}
			if(flag){
				if(checkAgent(ExcelUtil.getCellText(hssfRow.getCell(7)).trim(),ExcelUtil.getCellText(hssfRow.getCell(8)).trim(),ExcelUtil.getCellText(hssfRow.getCell(9)).trim())){
					errorMsg.append("总对总销售渠道信息第 "+(rowNum+1)+" 行，归属渠道，渠道类型，新业务来源信息不匹配，请检查 <br>");
					errorCount++;
					continue;
				}
			}
			//校验导入出单机构代码：是否为登陆用户所属分公司下渠道信息
			if(!"00".equals(departmentStr) && !departmentStr.equals(ExcelUtil.getCellText(hssfRow.getCell(6)).trim().substring(0,2))){
				errorMsg.append("总对总销售渠道信息第 "+(rowNum+1)+" 行，出单机构代码"+ExcelUtil.getCellText(hssfRow.getCell(6)).trim()+"不存在或没有相关权限，请检查修改<br>");
				errorCount++;
				continue;
			}
			//为了弹出界面简洁，限制输出10个错误；
			if(errorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			//取单元格值
			String zone ="";
			String cardApplicant ="";
			//String agentType = ExcelUtil.getCellText(hssfRow.getCell(0)).replaceAll("\\s*", "");
			//非必录特殊处理
			if(null!=hssfRow.getCell(0) && StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(0)).trim())){
				zone = ExcelUtil.getCellText(hssfRow.getCell(0)).trim();
			}
			String branch = ExcelUtil.getCellText(hssfRow.getCell(1)).trim();
			String branchName = ExcelUtil.getCellText(hssfRow.getCell(2)).trim();
			String userCode = ExcelUtil.getCellText(hssfRow.getCell(3)).trim();
			String userName = ExcelUtil.getCellText(hssfRow.getCell(4)).trim();
			String channelCode = ExcelUtil.getCellText(hssfRow.getCell(5)).trim();
			String dptCde = ExcelUtil.getCellText(hssfRow.getCell(6)).trim();
			
			String channleCode = ExcelUtil.getCellText(hssfRow.getCell(7)).trim();
			String channleType = ExcelUtil.getCellText(hssfRow.getCell(8)).trim();
			String newBsnsType = ExcelUtil.getCellText(hssfRow.getCell(9)).trim();
			String categoryCode = ExcelUtil.getCellText(hssfRow.getCell(10)).trim();
			
			String agentNo = ExcelUtil.getCellText(hssfRow.getCell(11)).trim();
			String protocolNo = ExcelUtil.getCellText(hssfRow.getCell(12)).trim();
			String cbrkrname = ExcelUtil.getCellText(hssfRow.getCell(13)).trim();
			String serviceCode = ExcelUtil.getCellText(hssfRow.getCell(14)).trim();
			String serviceName = ExcelUtil.getCellText(hssfRow.getCell(15)).trim();
			String empNo = ExcelUtil.getCellText(hssfRow.getCell(16)).trim();
			String empName = ExcelUtil.getCellText(hssfRow.getCell(17)).trim();
			String empPhone = ExcelUtil.getCellText(hssfRow.getCell(18)).trim();
			String repairCode=ExcelUtil.getCellText(hssfRow.getCell(19)).trim();
			String repairName=ExcelUtil.getCellText(hssfRow.getCell(20)).trim();
			if(null!=hssfRow.getCell(21) && StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(21)).trim())){
				cardApplicant = ExcelUtil.getCellText(hssfRow.getCell(21)).trim();
			}
			String teamCode = ExcelUtil.getCellText(hssfRow.getCell(22)).trim();
			String teamName = ExcelUtil.getCellText(hssfRow.getCell(23)).trim();
			String coprcod = ExcelUtil.getCellText(hssfRow.getCell(24)).trim();
			String organization = ExcelUtil.getCellText(hssfRow.getCell(25)).trim();
			String cAgentCode=ExcelUtil.getCellText(hssfRow.getCell(26)).trim();			
			String cptccNumber=ExcelUtil.getCellText(hssfRow.getCell(27)).trim();
			String cPhone=ExcelUtil.getCellText(hssfRow.getCell(28)).trim();
			String cemploYee=ExcelUtil.getCellText(hssfRow.getCell(29)).trim();
			String cBrkrCertfCde=ExcelUtil.getCellText(hssfRow.getCell(30)).trim();
			String cSlsIdentItyNo=ExcelUtil.getCellText(hssfRow.getCell(31)).trim();
			String cSaleName=ExcelUtil.getCellText(hssfRow.getCell(32)).trim();
			TradAgentInfo temObj = new TradAgentInfo();
			temObj.setAgentType("4");//由于之前已经校验，可以set 固定值；
			temObj.setExcelRowIndex(rowNum+1);
			if(StrUtil.isNotEmpty(zone)){
				temObj.setZone(zone);
				temObj.setSubChannelCode(channelCode.concat(zone).concat(branch));
			}else{
				temObj.setSubChannelCode(branch);
			}
			temObj.setRepairCode(repairCode);
			temObj.setRepairName(repairName);
			temObj.setBranch(branch);
			temObj.setBranchName(branchName);
			temObj.setUserCode(userCode);
			temObj.setUserName(userName);
			temObj.setChannelCode(channelCode);
			temObj.setDptCde(dptCde);
			temObj.setChannleCode(channleCode);
			temObj.setChannleType(channleType);
			temObj.setNewBsnsType(newBsnsType);
			temObj.setCategoryCode(categoryCode);
			temObj.setAgentNo(agentNo);
			temObj.setProtocolNo(protocolNo);
			temObj.setServiceCode(serviceCode);
			temObj.setServiceName(serviceName);
			temObj.setCbrkrname(cbrkrname);
			temObj.setCoprcod(coprcod);
			temObj.setEmpNo(empNo);
			temObj.setEmpName(empName);
			temObj.setOrganization(organization);
			if(StrUtil.isNotEmpty(cardApplicant)){
				temObj.setCardApplicant(cardApplicant);
			}
			temObj.setTeamCode(teamCode);
			temObj.setTeamName(teamName);
			temObj.setOperUser(user.getUserid());
			temObj.setcAgentCode(cAgentCode);
			temObj.setCptccNumber(cptccNumber);
			temObj.setcPhone(cPhone);
			temObj.setCemploYee(cemploYee);
			temObj.setcBrkrCertfCde(cBrkrCertfCde);
			temObj.setcSlsIdentItyNo(cSlsIdentItyNo);
			temObj.setcSaleName(cSaleName);
			temObj.setEmpPhone(empPhone);
			temLs.add(temObj);
		}
		//3、校验excel中的子机构代码不能重复
		int checkUserCodeErrorCount=0;
		for(int i = 0; i < temLs.size() - 1; i++){
			String checkSubChannelCode = temLs.get(i).getSubChannelCode();
			if(checkUserCodeErrorCount>10){
				throw new AppException("上传失败:<br>"+errorMsg.toString());
			}
			for(int j = i + 1; j < temLs.size(); j++){
				if(checkSubChannelCode.equals(temLs.get(j).getSubChannelCode())){
					errorMsg.append("总对总销售渠道信息 第  "+(i+3)+" 行与第 "+(j+3)+" 行合作伙伴、区域代码、网点代码列组成的唯一标识重复，请修改 <br>");
					checkUserCodeErrorCount++;
					continue;
				}
			}
		}
		if(StrUtil.isNotEmpty(errorMsg.toString())){
			throw new AppException("上传失败:<br>"+errorMsg.toString());
		}
		return temLs;
	}

	@Override
	public Result saveAgentMtInfo(String flag, String agentType,
			String userCode, String channelCode, String subChannelCode,
			String dptCde, String agentNo, String protocolNo,
			String serviceCode,String serviceName, String empNo,String empName, String teamCode, String teamName,
			String userName, String cardApplicant, String channleCode,
			String channleType, String newBsnsType, String categoryCode,
			String zone, String organization, String branch, String branchName, Long id,
			GatewayUser user,String cbrkrname,String coprcod,
			String key1,String key2,String key3,String key4,String key5,String unionType) throws Exception {
		
		String temSubCode="";
		String regexM = "[A-Za-z0-9_]+";//匹配正则数字、字符、下划线
		GatewayChannelInfo channelInfo = new GatewayChannelInfo();
		channelInfo.setChannelCode(channelCode);
		GatewayChannelInfo channelInfoFind = channelInfoConfigDao.queryByChannelInfo(channelInfo);
		//调用交易系统 检查信息是否正确
		TradAgentInfo checkAgentMtInfo = new TradAgentInfo(); //只用作校验
		checkAgentMtInfo.setDptCde(dptCde);
		checkAgentMtInfo.setEmpNo(empNo);
		checkAgentMtInfo.setTeamCode(teamCode);
		checkAgentMtInfo.setNewBsnsType(newBsnsType);
		checkAgentMtInfo.setChannleType(channleType);
		checkAgentMtInfo.setAgentNo(agentNo);
		checkAgentMtInfo.setProtocolNo(protocolNo);
		checkAgentMtInfo.setServiceCode(serviceCode);

		Map<String, String> resultMap = agentInfoValidation(checkAgentMtInfo);
		if (!"0000".equals(resultMap.get("result"))){
			return Result.error(resultMap.get("result"));
		}
		//个人
		if("3".equals(agentType)){
			// 检查是否已经存在
			if(!userCode.matches(regexM)){
				return Result.error("输入的代理人用户名格式不正确，正确格式只能录入字母、数字、下划线组合的字符串");
			}
			//同一合作伙伴内用户名不重复即可
			if("save".equals(flag)){
				TradAgentInfo agent = new TradAgentInfo();
				agent.setUserCode(userCode);
				agent.setChannelCode(channelCode);
				List<TradAgentInfo> list = agentInfoMtDao.queryAgentInfoByMap(agent);
				if(null!=list && !list.isEmpty()){
					return Result.error("同一合作伙伴内，该代理用户名已存在,请更换");
				}
			}
			if("1".equals(channelInfoFind.getTransType())){//合作伙伴属于系统对接，则个人销售信息不添加t_Facade_user表    交易类型 1为界面出单 2为系统对接
				FacadeUser facadeUser = new FacadeUser();
				facadeUser.setUserCode(userCode);
				facadeUser.setUserName(userName);
				facadeUser.setUserTypeCode(agentType);
				if(StrUtil.isNotEmpty(subChannelCode)){
					facadeUser.setfKSubChannelCode(subChannelCode);
				}else{
					facadeUser.setfKSubChannelCode(channelCode);
				}
				facadeUser.setFkChannelCode(channelCode);
				facadeUser.setStatus(1);
				facadeUser.setUserPwd(MD5Util.md5(userCode));
				facadeUser.setOperTime(new Date());
				facadeUser.setOperUser(user.getUserid());
				if("save".equals(flag)){
					FacadeUser serchUser = new FacadeUser();
					serchUser.setUserCode(userCode);
					List<FacadeUser> users = userdao.queryUsersByMapUser(serchUser);
					if(users!=null&&!users.isEmpty()){
						return Result.error("该代理用户名已存在,请更换");
					}
					userdao.addUser(facadeUser);
				}else if("update".equals(flag)){
					Map<String,String> map = new HashMap<String, String>();
					map.put("userCode", userCode);
					FacadeUser temUser = userdao.queryUserCountByMap(map);
					if(null!=temUser){
						facadeUser.setUserid(temUser.getUserid());
						userdao.saveUser(facadeUser);//更新操作时处理
					}
				}
			}
		}
		//总对总
		if("4".equals(agentType)){
			//首先先检查否存在该子机构的信息
			GatewaySubChannel subChannelInfo = new GatewaySubChannel();
			if(StrUtil.isNotEmpty(zone)){
				temSubCode = channelCode.concat(zone).concat(branch);
				subChannelInfo.setSubChannelCode(temSubCode);
			}else{
				temSubCode = branch;
				subChannelInfo.setSubChannelCode(temSubCode);
			}
			subChannelInfo.setSubChannelName(branchName);
			subChannelInfo.setParentChannelCode(channelCode);
			subChannelInfo.setZone(zone);
			subChannelInfo.setBranch(branch);
			subChannelInfo.setBranchName(branchName);
			subChannelInfo.setFkChannelCode(channelCode);
			subChannelInfo.setStatus("1");
			subChannelInfo.setOperUser(user.getUserid());
			subChannelInfo.setOperTime(new Date());
			Map<String, String> map1=new HashMap<String, String>();
			map1.put("subChannelCode", subChannelInfo.getSubChannelCode());
			map1.put("chanelCode", subChannelInfo.getFkChannelCode());
			if("save".equals(flag)){
				if(null!=subChannelDao.querySubChannelInfoByCondition(map1)){
					if(temSubCode.equals(branch)){
						return Result.error("该网点代码已经存在,请更换");
					}else{
						return Result.error("本合作伙伴下的区域代码和网点代码配置重复,请检查");
					}
				}
				//总对总界面出单处理
				if(null!=channelInfoFind && "1".equals(channelInfoFind.getTransType())){
					if(StrUtil.isNotEmpty(userCode) && StrUtil.isEmpty(userName)){
						return Result.error("姓名不允许为空，请检查");
					}else if(StrUtil.isNotEmpty(userName) && StrUtil.isEmpty(userCode)){
						return Result.error("用户名不允许为空，请检查");
					}else if(StrUtil.isNotEmpty(userName) && StrUtil.isNotEmpty(userCode)){
						if(!userCode.matches(regexM)){
							return Result.error("输入的代理人用户名格式不正确，正确格式只能录入字母、数字、下划线组合的字符串");
						}
						FacadeUser serchUser = new FacadeUser();
						serchUser.setUserCode(userCode);
						List<FacadeUser> users = userdao.queryUsersByMapUser(serchUser);
						if(users!=null&&!users.isEmpty()){
							return Result.error("用户名 "+userCode+"已存在,请更换");
						}
						FacadeUser facadeUser = new FacadeUser();
						facadeUser.setUserCode(userCode);
						facadeUser.setUserName(userName);
						facadeUser.setUserTypeCode(agentType);
						facadeUser.setfKSubChannelCode(subChannelInfo.getSubChannelCode());
						facadeUser.setStatus(1);
						facadeUser.setFkChannelCode(channelCode);
						facadeUser.setUserPwd(MD5Util.md5(userCode));
						facadeUser.setOperTime(new Date());
						facadeUser.setOperUser(user.getUserid());
						userdao.addUser(facadeUser);
					}
				}
				if("save".equals(flag)){
					subChannelDao.save(subChannelInfo);
				}
			}else if("update".equals(flag)){
				if(StrUtil.isNotEmpty(userCode)){
					Map<String,String> map = new HashMap<String, String>();
					map.put("userCode", userCode);
					FacadeUser temUser = userdao.queryUserCountByMap(map);
					if(null!=temUser){
						FacadeUser facadeUserToDB = new FacadeUser();
						facadeUserToDB.setUserid(temUser.getUserid());
						facadeUserToDB.setUserName(userName);//总对总只能修改出单用户姓名
						userdao.saveUser(facadeUserToDB);//更新操作时处理
					}
				}
				if("update".equals(flag)){
					subChannelDao.update("updateGatewaySubChannel", subChannelInfo);
				}
			}
		}
		//联合出单处理
		if("5".equals(agentType)){
			if("save".equals(flag)){
				Map<String, String> map = new HashMap<String, String>();
				map.put("unionType", unionType);
				map.put("key1", key1);
				map.put("key2", key2);
				map.put("key3", key3);
				map.put("key4", key4);
				map.put("key5", key5);
				if(agentInfoMtDao.queryAgentInByMap(map).size()>0){
					return Result.error("key配置重复，请更改！");
				}
			}
		}
		
		TradAgentInfo agentMtInfo = new TradAgentInfo();
		agentMtInfo.setAgentType(agentType);
		agentMtInfo.setUserCode(userCode);
		agentMtInfo.setUserName(userName);
		agentMtInfo.setChannelCode(channelCode);
		if("3".equals(agentType)){
			agentMtInfo.setSubChannelCode(subChannelCode);
		}
		if("4".equals(agentType)){
			agentMtInfo.setSubChannelCode(temSubCode);
		}
		if("5".equals(agentType)){
			agentMtInfo.setKey1(key1);
			agentMtInfo.setKey2(key2);
			agentMtInfo.setKey3(key3);
			agentMtInfo.setKey4(key4);
			agentMtInfo.setKey5(key5);
			agentMtInfo.setUnionType(unionType);
		}
		agentMtInfo.setDptCde(dptCde);
		agentMtInfo.setAgentNo(agentNo);
		agentMtInfo.setProtocolNo(protocolNo);
		agentMtInfo.setServiceCode(serviceCode);
		agentMtInfo.setEmpNo(empNo);
		agentMtInfo.setTeamCode(teamCode);
		agentMtInfo.setTeamName(teamName);
		//add by menyanlei 新意见险新增
		agentMtInfo.setServiceName(serviceName);
		agentMtInfo.setEmpName(empName);
		//add  end
		agentMtInfo.setCardApplicant(cardApplicant);
		agentMtInfo.setChannleCode(channleCode);
		agentMtInfo.setChannleType(channleType);
		agentMtInfo.setNewBsnsType(newBsnsType);
		agentMtInfo.setCategoryCode(categoryCode);
		agentMtInfo.setOperTime(new Date());
		agentMtInfo.setCoprcod(coprcod);
		agentMtInfo.setCbrkrname(cbrkrname);
		agentMtInfo.setOperUser(user.getUserid());
		agentMtInfo.setOrganization(organization);

/*		//调用交易系统 检查信息是否正确
		Map<String, String> resultMap = agentInfoValidation(agentMtInfo);
		if (!"0000".equals(resultMap.get("result"))){
			return Result.error(resultMap.get("result"));
		}*/
		if("save".equals(flag)){
			if(agentInfoMtDao.addAgentInfo(agentMtInfo)){
				clearAllCacheByChannelCode(channelCode);
				return Result.success("添加成功！");
			}
			return Result.error("添加失败！");
		}else if("update".equals(flag)){
			agentMtInfo.setId(id);
			if(agentInfoMtDao.updateAgentInfo(agentMtInfo)){
				clearAllCacheByChannelCode(channelCode);
				return Result.success("更新成功！");
			}
			return Result.error("更新失败！");
		}else{
			return Result.error("操作失败，系统检查出非法数据传入，请检查！");
		}

	}

	public List<TradSaleAgent> queryTrandSaleAgent(String id) throws Exception {
		
		return agentInfoMtDao.queryTrandSaleAgent(id);
	}

	@Override
	public void deleteTrade(String id) throws Exception {
		agentInfoMtDao.deleteTrade(id);
	}

	@Override
	public boolean addTrade(List<TradSaleAgent> saleAgents) throws Exception {
		return agentInfoMtDao.addTrade(saleAgents);
	}
	//合作伙伴配置缓存管理公共方法
	public void clearAllCacheByChannelCode(String channelCode) throws Exception {
		//查询应用by：channelCode
		List<GatewayChannelApp> appLs = gatewayChannelAppDao.queryChannelAppInfoListByChannelCode(channelCode);
		if(null!=appLs && appLs.size()>0){
			for(GatewayChannelApp tem :appLs){
				gatewayClearCacheManager.clearAllCache(tem.getAppId());
			}
		}
	}

	@Override
	public Page<CoreDept> queryPageForCoreDept(Page<CoreDept> page,
			String partnerNo, String agentType , GatewayUser gatewayuser) throws Exception {
		return deptDao.queryCoreDeptPage1(page,partnerNo , gatewayuser);
	}

	
	
	public Map<String,List<TradAgentInfoRela>> importCarHandlingFee(HSSFSheet sheet,
			GatewayUser user) throws AppException{
		List<TradAgentInfoRela> temLs = new ArrayList<TradAgentInfoRela>();
		List<TradAgentInfoRela> updateList = new ArrayList<TradAgentInfoRela>();
		List<TradAgentInfoRela> alreadyExistsList = new ArrayList<TradAgentInfoRela>();
		List<TradAgentInfoRela> alreadyExistsInfo = new ArrayList<TradAgentInfoRela>();
		List<TradAgentInfoRela> carPlanNoList = new ArrayList<TradAgentInfoRela>();
		List<TradAgentInfoRela> carPlanNoInfo = new ArrayList<TradAgentInfoRela>();
		Map<String, List<TradAgentInfoRela>> map = new HashMap<String, List<TradAgentInfoRela>>();
		String partnerCode = "";//合作伙伴代码拼接
		String subChannelCode="";//网点代码拼接
		int count = sheet.getLastRowNum();//新增车险手续费信息条数
		int listCount=0;
		
		if(count/200<=0){
			listCount=1;
		}else if(count/200>=0){
			listCount=count/200;
		}
		List<Integer> checkDBCount = new ArrayList<Integer>(listCount);
		for(int i=1;i<=listCount;i++){ 
			checkDBCount.add(Integer.valueOf(i*200));
		}
		for (int rowNum = 3; rowNum <= count; rowNum++) {
			HSSFRow hssfRow = sheet.getRow(rowNum);
				String prodType = ExcelUtil.getCellText(hssfRow.getCell(0)).trim();//产品类型
				TradAgentInfoRela tradAgentInfoRela = new TradAgentInfoRela();
				if(StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(1)).trim())){// 手续费上限
					tradAgentInfoRela.setNfeeProdMax(new BigDecimal(ExcelUtil.getCellText(hssfRow.getCell(1)).trim()));
				}
				if(StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(2)).trim())){
					tradAgentInfoRela.setNfeeProdMin(new BigDecimal(ExcelUtil.getCellText(hssfRow.getCell(2)).trim()));// 手续费下限
				}
				if(StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(5)).trim())){
					tradAgentInfoRela.setCommRate(new BigDecimal(ExcelUtil.getCellText(hssfRow.getCell(5)).trim()));// 手续费比例
				}
				if(StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(6)).trim())){
					tradAgentInfoRela.setMarket(new BigDecimal(ExcelUtil.getCellText(hssfRow.getCell(6)).trim()));// 市场维护费比例
				}
				if(StrUtil.isNotEmpty(ExcelUtil.getCellText(hssfRow.getCell(7)).trim())){
					tradAgentInfoRela.setBusiness(new BigDecimal(ExcelUtil.getCellText(hssfRow.getCell(7)).trim()));//业务绩效比例
				}
				tradAgentInfoRela.setOperUser(user.getUserid());
				tradAgentInfoRela.setOperTime(new Date());
				tradAgentInfoRela.setProdType(prodType);
				tradAgentInfoRela.setChannelCode(ExcelUtil.getCellText(hssfRow.getCell(3)).trim());
				tradAgentInfoRela.setSubChannelCode(ExcelUtil.getCellText(hssfRow.getCell(4)).trim());
				if(!partnerCode.contains(ExcelUtil.getCellText(hssfRow.getCell(3)).trim())){
					partnerCode+="'"+ExcelUtil.getCellText(hssfRow.getCell(3)).trim()+"',";
				}
				if(!subChannelCode.contains(ExcelUtil.getCellText(hssfRow.getCell(4)).trim())){
					subChannelCode+="'"+ExcelUtil.getCellText(hssfRow.getCell(4)).trim()+"',";
				}
				
				//每200条查询一次
				if(checkDBCount.contains(rowNum)){
					try {
						 carPlanNoList = agentInfoRelaDao.selectCarPlanCodeInfoList(partnerCode.substring(0,partnerCode.length()-1),subChannelCode.substring(0,subChannelCode.length()-1));
						 carPlanNoInfo.addAll(carPlanNoList);
						 carPlanNoList.clear();
					} catch (Exception e1) {
						logger.info("查询车险方案信息",e1);
					}
					try {
						 alreadyExistsList = agentInfoRelaDao.selectAlreadyExistsCarInfoList(partnerCode.substring(0,partnerCode.length()-1), subChannelCode.substring(0,subChannelCode.length()-1));
						 alreadyExistsInfo.addAll(alreadyExistsList);
						 alreadyExistsList.clear();
					} catch (Exception e1) {
						logger.info("查询数据库是否已存在车险信息",e1);
					}
					partnerCode="";
					subChannelCode="";
				}
				
				temLs.add(tradAgentInfoRela);
		}
		//最后追加查询数据库
		if(StrUtil.isNotEmpty(partnerCode)&&StrUtil.isNotEmpty(subChannelCode)){
			try {
				 carPlanNoList = agentInfoRelaDao.selectCarPlanCodeInfoList(partnerCode.substring(0,partnerCode.length()-1),subChannelCode.substring(0,subChannelCode.length()-1));
				 carPlanNoInfo.addAll(carPlanNoList);
				 carPlanNoList.clear();
			} catch (Exception e1) {
				logger.info("查询车险方案信息",e1);
			}
			try {
				 alreadyExistsList = agentInfoRelaDao.selectAlreadyExistsCarInfoList(partnerCode.substring(0,partnerCode.length()-1), subChannelCode.substring(0,subChannelCode.length()-1));
				 alreadyExistsInfo.addAll(alreadyExistsList);
				 alreadyExistsList.clear();
			} catch (Exception e1) {
				logger.info("查询数据库是否已存在车险信息",e1);
			}
			partnerCode="";
			subChannelCode="";
		}
		
		//取出需要更新的车险信息
		if(alreadyExistsInfo.size()!=0){
			for(int i=0;i<alreadyExistsInfo.size();i++){
				for(int j=0;j<temLs.size();j++){
					if(alreadyExistsInfo.get(i).getChannelCode().equals(temLs.get(j).getChannelCode())&&alreadyExistsInfo.get(i).getSubChannelCode().equals(temLs.get(j).getSubChannelCode())){									
						temLs.get(j).setId(alreadyExistsInfo.get(i).getId());
						updateList.add(temLs.get(j));
						temLs.remove(j);
					}
				}
			}
		}
		
		
		//根据合作伙伴代码及产品类型设置方案号
		if(temLs.size()!=0){
			for(int i=0;i<temLs.size();i++){
				for(int j=0;j<carPlanNoInfo.size();j++){
					if(carPlanNoInfo.get(j).getChannelCode().equals(temLs.get(i).getChannelCode())&&carPlanNoInfo.get(j).getProdType().equals(temLs.get(i).getProdType())&&carPlanNoInfo.get(j).getSubChannelCode().equals(temLs.get(i).getSubChannelCode())){
						if(null != carPlanNoInfo.get(j).getAgentInfoId()){
							temLs.get(i).setInsuPlanNo(carPlanNoInfo.get(j).getInsuPlanNo());
							temLs.get(i).setAgentInfoId(carPlanNoInfo.get(j).getAgentInfoId());
						}
					}else{
						throw new AppException("车险费用信息上传失败:<br>"+"未查询到代理信息：合作伙伴:："+temLs.get(i).getChannelCode()+"，网点代码："+temLs.get(i).getSubChannelCode());
					}
				}
			}
		}
		map.put("add", temLs);
		map.put("update", updateList);
		return map;
	}

	@Override
	public void deleteTradAgentInfoRela(String id)throws Exception{
		agentInfoRelaDao.deleteAgentInfoRela(id);
	}
	
	private boolean checkAgentInfo(String code, int length) throws AppException{
		boolean flag=false;
		if(5==length){
			//新业务来源
			List<DictDto> newBSList = DictCache.getDict(Constants.DICT_NEW_BUSINESS_SOURCE);
			for (DictDto dictDto : newBSList) {
				if(code.equals(dictDto.getCode())){
					flag=true;
					break;
				}
			}
		}else if(1==length){
			//归属渠道
			List<DictDto> btcList = DictCache.getDict(Constants.DICT_BELONG_TO_CHANNEL);
			for (DictDto dictDto : btcList) {
				if(code.equals(dictDto.getCode())){
					flag=true;
					break;
				}
			}
		}else if(3==length){
			//渠道类型
			List<DictDto> channelList = DictCache.getDict(Constants.DICT_CHANNEL_TYPE);
			for (DictDto dictDto : channelList) {
				if(code.equals(dictDto.getCode())){
					flag=true;
					break;
				}
			}
		}
		return flag;
	}
	private boolean checkAgent(String channleCode, String channleType,String newBsnsType) throws AppException{
		boolean flag=false;
		if(!channleCode.equals(channleType.substring(0, 1))||!channleCode.equals(newBsnsType.substring(0, 1))||!channleType.equals(newBsnsType.substring(0, 3))){
			flag=true;
		}
		return flag;
	}

	private Map<String,String> agentInfoValidation(TradAgentInfo tradAgentInfo) throws Exception {
		System.out.println(tradAgentInfo);
		Map<String,Object> map = new HashMap<String, Object>();
		Map<String,String> resultMap = new HashMap<String, String>();
		SimpleDateFormat sf = new SimpleDateFormat("yyyyMMddHHmmssSSS");
		String code = sf.format(new Date());
		map.put("SERIALNUMBER",code);
		map.put("tradAgentInfo",tradAgentInfo);
		List<DictDto> newBusinessSource = DictCache.getDict("new_business_source");
		String agentType = getAgentTypeByNewBsnsType(tradAgentInfo.getNewBsnsType(),newBusinessSource);
		map.put("AGENTTYPE",agentType);
		map = getAgentPermitn(agentType, tradAgentInfo.getcAgentCode(),tradAgentInfo.getDptCde(),map);
		map.put("channleType",tradAgentInfo.getChannleType().substring(0,1));
		String xml = "";
		String responseXml = ""; //交易系统返回结果报文
		xml = FreeMarkerUtil.process("/app/saleESB/30005.ftl",map); //转化后的报文
		logger.debug("查询交易系统的报文:"+xml);
		responseXml = HttpConnectorUtil.interaction(Config.getProp("salemanage.check.url"),xml,tradAgentInfo.getChannelCode(),Constants.GW_CH_TX_30005);
		logger.debug("交易系统返回报文"+responseXml);
		Document document = DocumentHelper.parseText(responseXml);
		Element root = document.getRootElement();
		String RESMSGCODE = selectSingleNodeByRoot(root, "/XML/HEAD/RESMSGCODE");  //返回码
		if ("0000".equals(RESMSGCODE)){
			resultMap.put("result",RESMSGCODE);
		}else {
			//校验有错
			List<Element> body = root.selectNodes("/XML/BODY");
			String result = "";
			for (Element element : body) {
				Iterator<Element> iterator = element.elementIterator();
				while (iterator.hasNext()){
					String errorMsg = iterator.next().getText();
					if (!"1".equals(errorMsg) && !"0".equals(errorMsg)){
						result = result + errorMsg + "<br>";
					}
				}
			}
			resultMap.put("result",result);
		}
		return resultMap;
	}

	private String getAgentTypeByNewBsnsType(String newBsnsType,List<DictDto> newBusinessSource) {
		String agentType = "";
		for (DictDto dictDto : newBusinessSource) {
			if (newBsnsType.equals(dictDto.getCode())){
				if ("1900101".equals(dictDto.getOtherCode2())){
					agentType = "0";
					break;
				}else if ("1900203".equals(dictDto.getOtherCode2())) {
					agentType = "1";
					break;
				}else if ("1900201".equals(dictDto.getOtherCode2())) {
					agentType = "2";
					break;
				}else if ("1900202".equals(dictDto.getOtherCode2())) {
					agentType = "3";
					break;
				}else if ("1900301".equals(dictDto.getOtherCode2())) {
					agentType = "4";
					break;
				}
			}
		}
		return agentType;
	}
	//根据代理类型查看车险的中介机构许可证号
	public Map<String,Object> getAgentPermitn(String agentType, String cAgentCode, String dptCode, Map<String,Object> map) {
		if ("2".equals(agentType) || "3".equals(agentType) || "4".equals(agentType)){
			//专兼经业务
			if ("32".equals(dptCode.substring(0,2)) || "86".equals(dptCode.substring(0,2)) || "87".equals(dptCode.substring(0,2)) || "33".equals(dptCode.substring(0,2))){
				//（江苏、苏州、浙江）地区
				map.put("AGENT_PERMITNOF",cAgentCode);
			}else {
				//非（江苏、苏州、浙江）地区
				map.put("AGENT_PERMITNOD",cAgentCode);
			}
		}else {
			//非专兼经业务
			map.put("AGENT_PERMITNOB",cAgentCode);
		}
		return map;
	}

	public String selectSingleNodeByRoot(Element root,String path){
		Node node = root.selectSingleNode(path);
		if(node==null){
			return "";
		}else{
			return node.getText();
		}
	}
	
	
}
